Percent '%' of Total Within Hierarchy Drilldown

[Jkaelin]

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

[Drewdel]

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')
)

[Jkaelin · Accepted solution]

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,

[Drewdel]

Sad, I just posted and your solution post pops up 

[Jkaelin]

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