Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am trying to calculate:
1) Number of Departments where each customer has bought
2) For those customers who bought in 2 different departments, what is the total count of those departments
3) not sure if possible, create a matrix to represent the count combination
Here is the sample of the dataset:
customerID | Department | Amount | Date |
1 | Salad | 20 | 10-jan-21 |
1 | Burger | 9 | 10-jan-21 |
1 | Drinks | 13 | 10-jan-21 |
2 | Salad | 18 | 10-jan-21 |
2 | Drinks | 20 | 10-jan-21 |
3 | Coffee | 9 | 10-jan-21 |
4 | Salad | 5 | 10-jan-21 |
5 | Salad | 9 | 10-jan-21 |
5 | Burger | 6 | 10-jan-21 |
6 | Salad | 15 | 10-jan-21 |
6 | Burger | 20 | 10-jan-21 |
7 | Drinks | 12 | 10-jan-21 |
7 | Coffee | 10 | 10-jan-21 |
With this calculation I got the following table
Count Distinct Dep =
sumx(SUMMARIZE(Articles,Articles[Departments],"Count dep",count(cust[customerID])),[Count dep])
customerID | Count Deparments |
1 | 3 |
2 | 2 |
3 | 1 |
4 | 1 |
5 | 2 |
6 | 2 |
7 | 2 |
Now I would like to select only with Count department = 2
customerID | Distinct Deparments = 2 | Department | Department |
2 | x | Salad | Drinks |
5 | x | Salad | Burger |
6 | x | Salad | Burger |
7 | x | Drinks | Coffee |
And count the distinct values of the Departments for those clients, but these different calculation are not working...
Two Dep Count Distinct =
COUNTROWS (FILTER (VALUES (cust[customerID]), CALCULATE([Count Distinct Dep]=2) ))
This following one returns the correct number of clients buying in 2 departments, but when visualizing with Department list, it crashes.
Dep count 2 grouping =
COUNTROWS (
FILTER (
VALUES ( cust[customerID] ),
CALCULATE ( [Count Distinct Dep]=2)
))
This should be the desired output:
Count | |
Salad | 3 |
Burger | 2 |
Drinks | 2 |
Coffee | 1 |
It is being quite tricky to find information about how to create this matrix, where you can visualize what are the pairs combination. In other terms, the previous result buy providing more information about how the Departments relate eachother.
Salad | Burger | Drinks | Coffee | |
Salad | - | 2 | 1 | |
Burger | 2 | - | ||
Drinks | 1 | - | 1 | |
Coffee | 1 | - |
Hi,
This measure works
Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[customerID]),Data[customerID],"ABCD",COUNTROWS(Data),"EFGH",CALCULATE(COUNTROWS(Data),ALL(Data[Department]))),[ABCD]>0&&[EFGH]=2))
Hope this helps.
Hi,
This solution is not working for me. I get totally random values that does not make sense. Could you please explain a bit the DAX and name ABCD and EFGH in a way that I can understand what they calculate?
Thank you.
I do not what the reason could be. You can very clearly see in my screenshot that it works fine on the data that you had shared.
Thanks for your explanation Ashish.
Hi @tortugamarina ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
@tortugamarina , While showing at department level you have to igonre department filter .
example
Two Dep Count Distinct =
calculate(COUNTROWS (FILTER (VALUES (cust[customerID]), CALCULATE([Count Distinct Dep]=2) )), filter(allselected(cust), Table[cust] = max(Table[cust])))
Hi,
I don´t get the result desired. In fact, MAX function is not possible to use with a Table, but needs a specific column, and if I replace the cust for cust[customerID] it results in blank:
Two Dep Count Distinct =
calculate(COUNTROWS (FILTER (VALUES (cust[customerID]), CALCULATE([Count Distinct Dep]=2) )), filter(allselected(cust), cust[customerID] = max(cust[customerID])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |