Forum Discussion
Matrix Table Row subtotal different with each row
- 2 years ago
DISTINCTCOUNT is not an additive measure by nature. Rarely, it will be additive if there is no overlap between rows.
I recommend you to read these articles:
https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/
Typically, if you want to force to get distinct count in total rows also is like this ... Try and adjust the formula to your needs. Hope it helps!
ac = VAR c = {"a","b","c"} var _calc = CALCULATE(DISTINCTCOUNT(sales[cust_cd]), FILTER(sales, RELATED(cust[a]) in c && RELATED(cust[s]) in {'T'} && [Net Amt] >0)) --Basically, you are looping through all the values of your category, do the calculation for each value (row) and add var _calc2 = Sumx( values(Table1[Year Month Category]), CALCULATE(DISTINCTCOUNT(sales[cust_cd]), FILTER(sales, RELATED(cust[a]) in c && RELATED(cust[s]) in {'T'} && [Net Amt] >0))) RETURN IF ( HasOneValue( Table1[Year Month Category]), _calc, _calc2)
DISTINCTCOUNT is not an additive measure by nature. Rarely, it will be additive if there is no overlap between rows.
I recommend you to read these articles:
https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/
Typically, if you want to force to get distinct count in total rows also is like this ... Try and adjust the formula to your needs. Hope it helps!
ac =
VAR c = {"a","b","c"}
var _calc = CALCULATE(DISTINCTCOUNT(sales[cust_cd]), FILTER(sales, RELATED(cust[a]) in c
&& RELATED(cust[s]) in {'T'} && [Net Amt] >0))
--Basically, you are looping through all the values of your category, do the calculation for each value (row) and add
var _calc2 = Sumx( values(Table1[Year Month Category]), CALCULATE(DISTINCTCOUNT(sales[cust_cd]), FILTER(sales, RELATED(cust[a]) in c
&& RELATED(cust[s]) in {'T'} && [Net Amt] >0)))
RETURN IF ( HasOneValue( Table1[Year Month Category]), _calc, _calc2)
Hi sir,
Yes, this solution you provided is help! Thank you so much.
Thanks for the shared link.
I try to change it not using distinctcount.
Thanks.