Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Subtotal incorrect value

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!

amitchandak
6 years ago
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

3 Replies

amitchandak
Super User
6 years ago
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
  Not applicable
  6 years ago
  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.
v-xuding-msft
Community Support
6 years ago
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.

Related content