Show correct SUM in subtotal / grandtotal rows in a Matrix

[Sage477]

Hi, I am stuck with fixing the sub total for a matrix table I am using to create a report. The measure I created is using a switch function to check row category/level to provide specific calculation however I would like the sub total to sum all the values in each row in the table. 

Category	Measure
A	5
B	10
C	20
total	35

using the switch function If category is B then 2 * [value of A], if category is C, then its 4*[value of A] . The total should show the sum of the values in the table. 

[v-zhangtin-msft · Accepted solution]

Hi, @Sage477 

You can try the following methods.

Measure 1 = 
VAR _SumA=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Category]="A"))
RETURN
SWITCH(TRUE(),
SELECTEDVALUE('Table'[Category])="A",_SumA,
SELECTEDVALUE('Table'[Category])="B",2*_SumA,
SELECTEDVALUE('Table'[Category])="C",4*_SumA,BLANK())

Measure Result = 
Var _table=SUMMARIZE('Table','Table'[Category],"Sum",[Measure 1])
Var _Sumtotal=SUMX(_table,[Sum])
RETURN
IF(HASONEVALUE('Table'[Category]),[Measure 1],_Sumtotal)

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Sage477 

you can try this

Measure = SWITCH(SELECTEDVALUE('Table'[category]),"A",sum('Table'[value]),"B",CALCULATE(sum('Table'[value]),all('Table'[category]),'Table'[category]="A")*2,"C",CALCULATE(sum('Table'[value]),all('Table'[category]),'Table'[category]="A")*4,sum('Table'[value]))

[Sage477]

Hi thanks but the sub total is still showing incorrectly, it should add the values in the table. 

[ryan_mayu]

then you create another meausre

measure2=sumx(values('table'[catogry]),measure)