Forum Discussion
Matrix Table Row subtotal different with each row
Hi guys,
I creating a DAX formula with the multiple filter condition, this is total number for each row is correct but the Matrix Table Row Total different when calculate each row.
Below is my DAX formula
ac = VAR c = {"a","b","c"}
RETURN
CALCULATE(DISTINCTCOUNT(sales[cust_cd]), FILTER(sales, RELATED(cust[a]) in c
&& RELATED(cust[s]) in {'T'}
&& [Net Amt] >0))
Matrix Table
Year Month | ac
2023-08 | 8186
2023-09 | 8125
2023-10 | 2815
Total | 9227
By right, the result return
Year Month | ac
2023-08 | 8186
2023-09 | 8125
2023-10 | 2815
Total | 19126
Hope someone can help me. Thank you
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)
4 Replies
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.
ChaCha123 You can try using the ALL function using the following expressions:
ac =
VAR c = { "a", "b", "c" }
RETURN
CALCULATE (
DISTINCTCOUNT ( sales[cust_cd] ),
FILTER (
ALL ( sales ),
RELATED ( cust[a] ) IN c
&& RELATED ( cust[s] ) IN { 'T' }
&& [Net Amt] > 0
)
)
========== OR ======ac =
VAR c = {"a", "b", "c"}
RETURN
CALCULATE(
DISTINCTCOUNT(sales[cust_cd]),
FILTER( sales,
RELATED(cust[a]) IN c
&& RELATED(cust[s]) IN {"T"}
&& [Net Amt] > 0
),
ALL(sales) // This removes filter context on 'sales' for the total row
)
Please let me know if this helped.Thanks
Hi Sir,
Thank you so much replied my question. Apperciate it.
ac =
VAR c = { "a", "b", "c" }
RETURN
CALCULATE (
DISTINCTCOUNT ( sales[cust_cd] ),
FILTER (
ALL ( sales ),
RELATED ( cust[a] ) IN c
&& RELATED ( cust[s] ) IN { 'T' }
&& [Net Amt] > 0
)
)Result become:
Year Month | ac
2023-08 | 9227
2023-09 | 9227
2023-10 | 9227
Total | 9227
ac =
VAR c = {"a", "b", "c"}
RETURN
CALCULATE(
DISTINCTCOUNT(sales[cust_cd]),
FILTER( sales,
RELATED(cust[a]) IN c
&& RELATED(cust[s]) IN {"T"}
&& [Net Amt] > 0
),
ALL(sales) // This removes filter context on 'sales' for the total row
)Result:
Year Month | ac
2023-08 | 8186
2023-09 | 8125
2023-10 | 2815
Total | 9227
Still not get the result as per expected.
