cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
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.7BNot 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
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,

View solution in original post

4 REPLIES 4
Highlighted
Resolver I
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,

View solution in original post

Highlighted
Advocate II
Advocate II

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

Sad, I just posted and your solution post pops up Smiley LOL

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors