MATRIX distinct

[PowerKoen]

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?

[Michiel · Accepted solution]

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.)

[Michiel · Accepted solution]

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.)

[PowerKoen · Accepted solution]

Thanks, that did the trick but with this code it was a bit better and accurate

COUNTROWS(SUMMARIZE(customers,customers[Date],customers[customercode]))

[AnthonyTilley]

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