Subtotal incorrect value

[Anonymous]

Hi,
I need a sub total in each group, but why the calculation incorrect. I use matrix table for this report and already set sub total

My DAX calculation is:

ACC = VAR item_exps = MAX ( TEMPLATE[ITEM] )

VAR act_BOD = MAX ( TEMPLATE[MAN])

VAR act_NONBOD = MAX ( TEMPLATE[NON_MAN])

VAR act_GROUP = MAX ( TEMPLATE[GROUP])

VAR cc_exps = MAX ( CC_BI[CC])

VAR cc_exps_gr = max (CC_BI[CC_GROUP])

VAR cc_qad = MAX(EXPS[CC])

RETURN

SWITCH(TRUE(),

item_exps = "REPAIR MAINTENANCE BUILDING & EQUIP" && cc_exps_gr <> "BOD",

CALCULATE ( SUM ( EXPS[PERIOD_ACT]) /1000000.00 + 0,

FILTER(EXPS,EXPS[ACC_NUMB] = 61305 || EXPS[ACC_NUMB] = 61306),

FILTER(EXPS,EXPS[CC] = cc_exps )),

item_exps = "REPAIR MAINTENANCE BUILDING & EQUIP" && cc_exps_gr = "BOD",

CALCULATE ( SUM ( EXPS[PERIOD_ACT]) /1000000.00 + 0,

FILTER(EXPS,EXPS[ACC_NUMB] = 61305 || EXPS[ACC_NUMB] = 61306),

FILTER(EXPS,EXPS[CC] = "BOD1" || EXPS[CC] = "BOD2" )),

item_exps = "REPAIR MAINTENANCE VEHICLE" && cc_exps_gr = "BOD",

CALCULATE ( SUM ( EXPS[PERIOD_ACT]) /1000000.00 + 0,

FILTER(EXPS,EXPS[ACC_NUMB] = 61307 || EXPS[ACC_NUMB] = 61308),

FILTER(EXPS,EXPS[CC] = "BOD1" || EXPS[CC] = "BOD2" )),

item_exps = "REPAIR MAINTENANCE VEHICLE" && cc_exps_gr <> "BOD",

CALCULATE ( SUM ( EXPS[PERIOD_ACT]) /1000000.00 + 0,

FILTER(EXPS,EXPS[ACC_NUMB] = 61307 || EXPS[ACC_NUMB] = 61308),

FILTER(EXPS,EXPS[CC] = cc_exps )),

cc_exps_gr <> "BOD",

CALCULATE ( SUM ( EXPS[PERIOD_ACT]) / 1000000.00,

FILTER(EXPS,EXPS[ACC_NUMB] = act_NONBOD),FILTER(EXPS, EXPS[CC] = cc_exps) ) + 0

I use other table to get value in specific number,

Any help would be appreciated. Thanks!

[v-xuding-msft]

Hi @Anonymous ,

I agree with @amitchandak.  You could use the formula that he suggests to have a try. If you can't implement or you have other questions, please share some sample data and your expected output.  We will understand clearly about your situation.

[amitchandak · Accepted solution]

@Anonymous , when every you use if and switch then it needs a row context of table. and that context is missing at sub total and total level. If user filter clause then we remove that data and sum it up to we will get the correct subtotal.

If possible use filter in place of filter data in Switch, use a switch to change calculations

In the above case force a row conetxt from the lowest level like this example

sumx(summarize(Table,Table[class], table[country],"_1",[Budget]),[_1])

Here data is first groped at Table[class], table[country] level then used. You can have more or less level as per need

[Anonymous]

Hi All @amitchandak  @v-xuding-msft 

Thank you for your responded.

Idea provided by @amitchandak worked, I changes the rule of filter in group first to get value correctly and calculate it.

Thank you very much.