Forum Discussion

ChaCha123's avatar
ChaCha123
Frequent Visitor
2 years ago
Solved

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 ...
  • sevenhills's avatar
    sevenhills
    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/

    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)
DallasBaba's avatar
DallasBaba
Skilled Sharer
2 years ago

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's avatar
ChaCha123
Frequent Visitor
2 years ago

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.