topic Re: Matrix Table Row subtotal different with each row in DAX Commands and Tips

Matrix Table Row subtotal different with each row

ChaCha123 — Tue, 10 Oct 2023 11:56:04 GMT

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

Re: Matrix Table Row subtotal different with each row

sevenhills — Tue, 10 Oct 2023 18:19:08 GMT

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/

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907

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)

Re: Matrix Table Row subtotal different with each row

DallasBaba — Tue, 10 Oct 2023 18:27:46 GMT

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

Re: Matrix Table Row subtotal different with each row

ChaCha123 — Tue, 10 Oct 2023 23:31:53 GMT

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

Result:

Year Month | ac

2023-08 | 8186

2023-09 | 8125

2023-10 | 2815

Total | 9227

Still not get the result as per expected.

Re: Matrix Table Row subtotal different with each row

ChaCha123 — Tue, 10 Oct 2023 23:45:37 GMT

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.