Forum Discussion
Sum Hierarchy Levels based on selection
I have a hierarchy table and would like to display total based on what level the slicer is selected. If all Level 3 (Division) checkboxes selected, display sum of Level 2 (BU) total acro...
v-frfei-msft
Community Support
Community SupportHi Anonymous,
Unfortunately, it is really impossible to meet your requirement. However we can add normal slicers and create three measures to work around. Here are the formulas.
sum Div = IF(calculate(distinctcount([DMSION]),allselected(Table1[DMSION]))=CALCULATE(DISTINCTCOUNT(Table1[DMSION]),ALL(Table1)),SUM(Table1[BU_AMT]),CALCULATE(SUM(Table1[DIVISION_AMT]),ALLSELECTED(Table1[DMSION])))
group sum = IF(CALCULATE(DISTINCTCOUNT(Table1[Group]),ALLSELECTED(Table1[Group]))=CALCULATE(DISTINCTCOUNT(Table1[Group]),ALL(Table1)),SUM(Table1[GROUP_AMT]),CALCULATE(SUM(Table1[GROUP_AMT]),ALLSELECTED(Table1[Group])))
BU sum = IF(CALCULATE(DISTINCTCOUNT(Table1[BU]),ALLSELECTED(Table1[BU]))=CALCULATE(DISTINCTCOUNT(Table1[BU]),ALL(Table1)),SUM(Table1[GROUP_AMT]),CALCULATE(SUM(Table1[BU_AMT]),ALLSELECTED(Table1[BU])))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/ib2a3myc5fafbc6/suma2.pbix?dl=0
Regards,
Frank
Thanks for the feeback. I'll have to present this as an alternate. Do you think this can be accomplished with M Query? I've never done anything using M Query.