Forum Discussion
MATRIX distinct
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]))
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]))