cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

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

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

@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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors