I want create report in which will be sum of sales and percent from sum on group. I know in Power BI exists Quick measures, but they calculate percent from grand total, i need calculate percent from the above group.
Below is an example:
Solved! Go to Solution.
This is first time I'm facing with % total of multiple levels, so I separate it into 3 measures and use a trick with If condition to showing 3 measures for 3 levels.
% level 3 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Item]) ) )
% level 2 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Category]) ) )
% level 1 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Market]) ) )
As my observation, when showng 1 level in matrix, the parent will be 100% or 1, so I will use IF condition to replace this value to its percentage in the previous level
Level 2 = if([% level 3]=1 , [% level 2],[% level 3])
% group = if([Level 2]= 1,[% level 1] ,[Level 2])
It's late in Vietnam so i'm going to bed now, hope you take a look with some testing to ensure this is correct for all cases.
If this works for you please accept it as solution and also like to give KUDOS.
It is not identical. In the revised one, i have used the ALLSELECTED() function. I tried it and it worked fine.
I apologize, I should have posted an update. Yes, the latest measure is working. It was easier to see the correct percentages by turning off subtotals on the employee level. It is now clear that it is calculating correctly.
Thank you for all of your help!
You are welcome. If my reply helped, please mark it as Answer.
I am creating my first Power BI dashboard and I am having trouble with getting a percent formula working. We have data for manufacturing orders for several years, and we need to create an Labor Efficiency rate. The basic calculation is Cost put into WIP Labor/Cost Consumed Labor. I am using the this formula:
Efficiency Percent = DIVIDE(SlbMoStatus[Cost Put into WIP Labor],SlbMoStatus[Costs Consumed Labor])
I have set filters to select only year 2017 and for closed orders but my Efficiency rate results are way off.
What am I doing wrong?
Best Regards from USA,
There is no mistake in your formula. Show some data and your expected result.
That is the result for my formual. The correct total for January 2017 for only closed manufacturing orders should be 104%
I am attaching some sample date for you.
|Manufacturing Order||MO Status||Outsourced||Costs Consumed Labor||Cost Put into WIP Labor||Change Date||Total Costs Consumed||Total Cost Put into WIP|
Any help would be greatly appreciated.
You have created Efficiency Percent as a calculated column formula. You should delete it from there and create it as a Measure.
You are most welcome. Please mark my reply as Answer.
You can create a hierarchical measure like described here: http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!