Forum Discussion
MATRIX distinct
Hello,
I have the following colomn in a table:
-Date
-Name
-CustomerCode
-Weeknumber
I made a MATRIX, with rows CustomerCode, and value (distinctcount of customercode)
Week 1 Week 2 Week 3 Total
CustomerCode 1 1 1 1
CustomerCode 1 1 1
CustomerCode 1 1 1
CustomerCode 1 1 1
Total 3 2 4 4
The total below is correct, but the colomnsubtotal is not showing correctly (must add the values horizontality)
How can I achieve this?
Technically the subtotals are correct in that they just do the distinctcount on all the weeks together - so for one customer code, it will never be more than one. If you want to count the number of weeks each customer appears, you'll have to calculate by week and add these:
SUMX(VALUES([Weeknumber]),CALCULATE(DISTINCTCOUNT([CustomerCode]))
)
(Add appropriate table names etc.)
Thanks, that did the trick but with this code it was a bit better and accurate
COUNTROWS(SUMMARIZE(customers,customers[Date],customers[customercode]))
3 Replies
Technically the subtotals are correct in that they just do the distinctcount on all the weeks together - so for one customer code, it will never be more than one. If you want to count the number of weeks each customer appears, you'll have to calculate by week and add these:
SUMX(VALUES([Weeknumber]),CALCULATE(DISTINCTCOUNT([CustomerCode]))
)
(Add appropriate table names etc.)
Thanks, that did the trick but with this code it was a bit better and accurate
COUNTROWS(SUMMARIZE(customers,customers[Date],customers[customercode]))
dont distinct count the customer code as this will always give a one in your row total as you are using the customer code as the row header
So distinctcount(CustomerCode) would always give 1 in your row total
like wise distinctcount(weeknumber) would give you a 1 in your colunm total
instead use distinctcount(name) the Name filed is not used in your table headers or rows should will produce the correct result and this should solve your problem
