Power Pivot - DISTINCOUNT based on criteria

[mrhk22]

I am using a DISTINCOUNT measure to count (for a team of sales people) the number of distinct customers who have made purchases.

I'd like to expand on this to count only the number of distinct customers who have made more than 3 purchases. How can something like this be achieved?

I know that I can just set the criteria in the pivot table field settings, but this won't let me do other things like time intelligence functions based on the value. Many thanks.

 

[arunsjain]

If you are using Power Pivot then try this:

= CALCULATE ( DISTINCTCOUNT ( [Customer] ), FILTER ( Table1, [Quantity] > 3 ) )

 

[mrhk22]

If you are using Power Pivot then try this:

= CALCULATE ( DISTINCTCOUNT ( [Customer] ), FILTER ( Table1, [Quantity] > 3 ) )

Thanks for the suggestion. However, I am working with a table of raw transactions data. Was hoping to be able to filter the quantity or purchases within a measure without having to create a separate table/field.

The big picture is that I'd want to track the number of repeat customers each sales person has, and how this trends over time (with a time intelligence function like a moving average)

 

[arunsjain]

Thanks for the suggestion. However, I am working with a table of raw transactions data. Was hoping to be able to filter the quantity or purchases within a measure without having to create a separate table/field.

The big picture is that I'd want to track the number of repeat customers each sales person has, and how this trends over time (with a time intelligence function like a moving average)

Hi,

That was a measure and it will not add any additional column in your existing table.

Can you upload some dummy raw data and explain what the output should be look like?

Cheers!!!

 

[mrhk22]

Hi,

That was a measure and it will not add any additional column in your existing table.

Can you upload some dummy raw data and explain what the output should be look like?

Cheers!!!

Hi and thanks for the help. This is how the tables would be set up.

1. I'd basically want to end up with a table listing "Sales Person" and the count of "Client" where the number of Transactions is >3 within the past 12 months. "Activity ID" is a unique ID for each transaction.

2. I'm also trying to link a table of Sales Assignments for further analysis, but the problem is that each client will have multiple sales people assigned. I tried to set it up with 2 intermediate tables that have unique client and unique sales names. This gets rid of the errors, but ends up giving the same values across all sales person selections.

Many thanks.