I have a report that includes sales codes and I need my visuals to show the distinct count of the sales codes. I used to filter by month and then realized than in the distinct count formula I need to force a row by row context, as whenever I applied the month filter the visual distinct count total didn´t match the row by row sum.
I applied this formula and when filtering by month, the row by row sum was equal to the distinct count total:
SumX Measure =
SUMX(
VALUES( 'Dates Table'[YearMonthnumber] ),
CALCULATE(
DISTINCTCOUNT( '2020'[Sales Code] )
)
)
However, now I need to use other filters rather than only the month, but when I filter according to other variables, again I have the problem that the distinct count total is not matching the row by row sum. I need the visual to have the correct distinct count total (matching with the row by row sum) by selecting different filters.
I just want the distinct count to be correct according to the filters I select, there are four filters from the same table (product, country, company, customer ID) and one filter from a dates table. I attach an example so you can better understand what I need to do, my file includes the filters I usually apply and other dax formulas I have tried: Distinct Count Error
Best
JLV