Forum Discussion
Distinct Count (customer) x Brand x Threshold
Hello guys,
I am struggling to find a way to make the following calculation.
I need to count the number of active customer per month per each brand, but each brand has a specific threshold. For instance:
Customer 1789 ordered 5 units of Brand X
Customer 9867 ordered 3 units of Brand X
Customer 2341 ordered 30 units of Brand X
Customer 2341 ordered 12 units of Brand Y
Considering the threshold for product X is >=5 and for Y is >=10 I would have two customers for Brand X (1789 and 2341) and one customer for Brand Y (2341).
I am using this function, but when I put the second condition (brand) it does not work...
=CALCULATE
(
DISTINCTCOUNT([Cliente]);
DATESBETWEEN(Tabela1[Data]; "01/01/2015"; "31/12/2015");
Tabela1[Quantidade]>=5
)
Any help from the genius out there? :)
3 Replies
- Phil_Seamark
Microsoft Employee
Can you please post a small sample set of your data (including column names) and we can try and create a calculated measure for you.
Hello, Phil
This is a piece of the data:
Order # Date Customer Brand Quantity 469669 08/02/2017 1009 CRD 3 469678 10/02/2017 1009 CRD 2 469669 12/02/2017 1009 CRD -3 470601 12/02/2017 1015 JIM 1 470601 12/02/2017 1015 JIM 2 470601 12/02/2017 1015 JIM 2 470612 12/02/2017 1015 CEL 9 470613 12/02/2017 1015 CEL 2 470612 12/02/2017 1015 CEL -1 470833 14/02/2017 1061 CRD 5 470833 14/02/2017 1061 FEN 9 470833 14/02/2017 1061 FEN 1 470887 14/02/2017 1262 JIM 3 470887 14/02/2017 1262 JIM 2 CRD THRESHOLD >=5 JIM THRESHOLD >=5 CEL THRESHOLD >=10 FEN THRESHOLD >=10 Considering the threshold for each brand, the final result should be:
2 customers for brand JIM (1015 and 1262)
1 customer for brand CEL (1015)
1 customer for brand CRD (1061) we need to discard the customer 1009 considering the return of 3 units, therefore the final result is 2 no reaching the threshold
1 customer for brand FEN (1061)
I hope this helps you to help me :)
- Phil_Seamark
How does this look?
There is an element of hardcoding the rules as you can see. These would be better kept in a table so you can update. Unless you know for sure they never change.
Add a new table as follows: I assumed the table you provided was called 'Brand'
Summary Table = FILTER( SUMMARIZE( 'Brand', Brand[Brand], Brand[Customer], "Orders",sum(Brand[Quantity]) ), ([Brand] = "JIM" && [Orders]>=5) || ([Brand] = "CRD" && [Orders]>=5) || ([Brand] = "CEL" && [Orders]>=10) || ([Brand] = "FEN" && [Orders]>=10) )
