Matrix Table Row subtotal different with each row

[ChaCha123]

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

[DallasBaba]

@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

[ChaCha123]

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.

[sevenhills · Accepted solution]

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

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)
           

[ChaCha123]

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.