Incorrect Total for DAX Measure in Matrix

[VulnBi]

Hello,

I use the following DAX formula which is giving me incorrect grand totals in my Matrix in Power Bi. The values in the rows are correct, but how it's summarized is not. The correct total would be: 30084

I am new to DAX so any hint or advice would be much appreciated.

Measure = COUNT(Table1[Field1]) * COUNT(Table2[Field2])

[FreemanZ · Accepted solution]

hi @VulnBi 

supposing you have fieldx in table1 as the row for the matrix, try like:

Measure =

SUMX(

VALUES(table1[fieldx]),

COUNT(Table1[Field1]) * COUNT(Table2[Field2])

)

[Anonymous · Accepted solution]

Hi FreemanZ ,thanks for the quick reply, I'll add more.

Hi @VulnBi ,

The total row does not have a filtering effect; it essentially multiplies the number of rows in your entire table, which results in such a large value. Also, the calculation of the total row is based on your measure, and since you want to get the sum, but your calculation is currently a multiplication, you should modify your DAX expression.

Like this

Measure = 
VAR _istotalrow = SELECTEDVALUE('Table'[ID])
VAR _resultSet = SUMMARIZE('Table','Table'[ID],"Result",COUNT('Table'[Field1]) * COUNT('Table'[Field2]))
RETURN 
IF(ISBLANK(_istotalrow),SUMX(_resultSet,[Result]),COUNT('Table'[Field1]) * COUNT('Table'[Field2]))

The '_resultSet' is a virtual table that stores the results of the calculations in advance, and finally uses the 'Selectedvalue' function to determine if the rows are totals. If so, the results in the virtual table are summed. If there is something you do not understand, feel free to ask questions.

Best Regards,
Wenbin Zhou

[FreemanZ · Accepted solution]

hi @VulnBi 

supposing you have fieldx in table1 as the row for the matrix, try like:

Measure =

SUMX(

VALUES(table1[fieldx]),

COUNT(Table1[Field1]) * COUNT(Table2[Field2])

)