number of sales per buyer / number of distinct buyer

[aymeric]

Hi community,

I have a classic sales Table, each row representing a sale, with the name of the buyer ("Buyer" column), and the date of the sale ("Date", column). Here's a sample below.

Buyer	Date
Jean	jan-17
Jean	feb-17
Jean	mar-17
Jean	apr-17
Jean	may-17
Pierre	jan-17
Pierre	feb-17
Pierre	mar-17
Pierre	may-17
Paul	jan-17
Paul	may-17

I'd like to create a table that provides the number of buyers who have the same number of sales, during a period I could set with a date slicer. 

Based on my previous example :

• for the period starting from january and ending in may, this would lead to 

number of sales per buyer	number of distinct buyers
2	1
4	1
5	1

• for the period starting from april and ending in may, this would lead to 

number of sales per buyer	number of distinct buyers
1	2
2	1

Any ideas ?

Many thanks in advance for your help !!

[v-yuezhe-msft]

@aymeric,

In your scenario, you would need to create the following measures and drag Buyer field to visual in order to get right result.

Number of sales per buyer = CALCULATE(COUNTROWS(Table),FILTER(Table,Table[Date]<=max(Table[Date])))
Number of buyers = DISTINCTCOUNT(Table[Buyer])

Regards,
Lydia

[aymeric]

Hi Lydia,

Thank you very much for your help.

Actually what I'm trying to get is a bit different from your suggestion.

Indeed my list of buyers contains more than 1 million names and I don't want them to appear in my visual. 

What I want to know (to be read directly in the visual) is how many distinct buyers bought the same number of products, for the period set via a date slicer.

If I withdraw the buyer field (as described in your solution), I can't get access anymore to the distribution of sales.

Thanks again in advance for your help on this.

Warm regards

[v-yuezhe-msft]

@aymeric,

I am afraid that your requirement can't be achieved. Because the "number of sales per buyer " measure and "number of distinct buyer" measure are evaluated based on the Buyer field, and they are dynamically filtered by Date slicer. Without Buyer , the two measures will be aggregated.

Regards,