Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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-Roll/m-p/411443

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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.