cancel
Showing results for
Did you mean:
Highlighted
Resolver I

## Percent '%' of Total Within Hierarchy Drilldown

Good morning,

Goal:  Trying to create a DAX measure that will calculate the % of Total when I am expanding down my hiearchy.

Details:  I have two dimensions on my hiearchy.  When I drill down to the more granular dimension, I'd like to see the % of total calculate - but it's just defaulting to 100%.  The image below may explain it better.  What I'd like to see, in the image below, row 2 'Target Retirement 2015 Fund' to be ~1.07% [\$93,710,129 / \$8,739,871,887 = ~1.07%].

Not all data shown in image that totals \$8.7B

Current Measure that does NOT work:

*Field: 'Plan Name Level 1' is the higher level & is part of the hierarchy

% of Plan Assets = DIVIDE([Asset AUM],CALCULATE([Asset AUM],ALL('Plans'[Plan Name Level 1])))
*I've also tried out SQLBI on hierarchy levels & other forum posts. For some reason, this just eclipses my skill set and I feel inexperienced for not able to solve it.  Happy to attach my pbix file but I don't seem to have an attachment option available.

Any thoughts?  Tips?  Help?

Thank you,
James

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I

Update:  I have figured out the solution curtiousy of SQL BI support:

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

The solution is this measure:

% of Plan Assets = DIVIDE([Asset AUM],CALCULATE([Asset AUM],ALL('Dimension')))

I don't understand the difference in DAX between using
ALL('Dimension') versus ALL('Dimension'[Plan Name Level1]) but it changes the results.

Thanks,
4 REPLIES 4
Highlighted
Resolver I

Update:  I have figured out the solution curtiousy of SQL BI support:

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

The solution is this measure:

% of Plan Assets = DIVIDE([Asset AUM],CALCULATE([Asset AUM],ALL('Dimension')))

I don't understand the difference in DAX between using
ALL('Dimension') versus ALL('Dimension'[Plan Name Level1]) but it changes the results.

Thanks,
Highlighted

I've actually done something like this but with a date hierarchy thanks to this article.  Here's the measure I came up with for you.  I think I got it right but without your file I couldn't really duplicate your data the same way, but it should work.  Hope this helps.

```% of Plan Assets =
VAR lvl1_margin =
DIVIDE(
[Asset AUM]
,CALCULATE(
[Asset AUM]
,ALLEXCEPT(
'Plans'
,'Plans'[1st lvl row]
)
)
)
VAR lvl2_margin =
DIVIDE(
[Asset AUM]
,CALCULATE(
[Asset AUM]
,ALLEXCEPT(
'Plans'
,'Plans'[1st lvl row]
,'Plans'[2nd lvl row]
)
)
)
VAR result =
IF(
lvl1_margin = 1
,lvl2_margin
,lvl1_margin
)
RESULT
CALCULATE(
result
,ALLSELECTED('Plans')
)```
Highlighted

Highlighted
Resolver I

@Drewdel  Thank you very much for taking the time & energy to help me with my problem.  I do appreciate it.  I've tried your solution and I'll need to play with it some more to get it to work correctly, but you are definiately close.  Thanks again.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors