Sum Hierarchy Levels based on selection

[Anonymous]

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 across all groups

If all Level 2(BU) checkboxes selected, display sum of Level 1(Group) total across all group

If not all Level 3 checkboxes selcted (regardless of BU), display selected Level 3 (Division) total across all BUs

If not all Level 2 checkboxes selcted (regardless of Group), display selected Level 2 (BU)total across all groups.

Below are some examples:

If Audit, SPU, YAD selected, display sum of (Audit, SPU, YAD)

If 7JB and F8C selected, display sum of (7jb and f8c)

If OC9 selected and Audit selected, display sum of (DIM, TIF and Audit)

This is the formula that I currently have.  It works for Divsion level and Group (Group works when no selection is made in the hierarchy)

myTotal = if (ISFILTERED(DataView[DIVISION]),[DIVISION_AMT],if(ISFILTERED(DataView[BU]),[BU_AMT],[GROUP_AMT]))

[Greg_Deckler]

Going to need example data for this one. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, take a look at my Matrix Measure Total Triple Threat Rock & Roll, it solves a similar problem to this and the techniques used might apply.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[v-frfei-msft]

Hi @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

[Anonymous]

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.