wrong subtotals in matrix visuals

[NISHA_S]

Hi everyone, 

I've got a problem with a matrix subtotals in the report.

For # of transaction column calaculation getting wrong subtotals in matrix table...I used some calcualtion for getting the # of transaction column.These column is used to calcualte count values.

VAR _Count1 =CALCULATE(DISTINCTCOUNT('table'[policy_or_group_reference]),FILTER('table',('table'[Transaction type] = "New Business" || 'table'[Transaction type] = "New MBR" || 'table'[Transaction type] = "New Reporter") && 'table'[endt_cert] = 0))
VAR _Count2 =CALCULATE(COUNTROWS(SUMMARIZE('table',
'table'[policy_or_group_reference] ,
'table'[endt_cert] )),FILTER('table',('table'[Transaction type] = "Endorsements" || 'table'[Transaction type] = "Cancellation" || 'table'[Transaction type] = "Reporter Endt") && ('table'[endt_cert] > 0 || 'table'[endt_cert] = 0)))
RETURN

if(isblank(_Count1), _Count2, _Count1)

These is the calcualtion i used while creating report...

Here ,total is calcualting  for 3 type only ie..New Business,New MBR,New Reporter only...balance is 3 type total is not considering....

21+1+3=25 getting which is not total for all type...

Plse help?

[NISHA_S · Accepted solution]

subtotal_transaction = if(HASONEVALUE('table'[Transaction type]),[# of transaction],SUMX(VALUES('table'[Transaction type]),[# of transaction]))

[v-yiruan-msft]

Hi @NISHA_S ,

You can create another new measure as below base on the current measure [# of transaction], then put it on your matrix to replace the measure [# of transaction] .

Measure = SUMX ( GROUPBY ( 'table'[MTD], 'table'[Transaction type] ), [# of transaction] )

In addition, you can refer the content in the following links to resolve your problem.

Power BI Shows Incorrect Measure Total? How to fix it?

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Best Regards

[amitchandak]

@NISHA_S , change return like give below, replace with correct un summarized columns in visual

sumx(summarize(Table, Table[MTD], Table[By transaction], "_1", if(isblank(_Count1), _Count2, _Count1)),[_1])