Power BI % of parent total & % of total in DAX/Power BI

[@erinfield1943]

0:24- using "Percent of Total", Power BI's UI way 1:05- Calculate the denominator first (the hard part) (measure). CALCULATE([Years on Throne]),ALL('English Monarchs')) 2:20- Numerator divided by Denominator. DIVIDE([Years on Throne],CALCULATE([Years on Throne]),ALL('English Monarchs')))*100 3:40- another way that will work with filters better. DIVIDE([Years on Throne],CALCULATE([Years on Throne],ALLSELECTED('English Monarchs'))

[@learnspreadsheets]

Thanks for the measures! Very helpful! Pinning your comment 😃

[@decentmendreams]

My brother, bless you . I have struggled for so long to calculate percentages. The last line with hasonevalue was a home run for me . I have tried few other examples but only yours I understood and was able to execute it without a hustle . Now I can go ahead and replace all my percentages with this newly acquired technique.

[@learnspreadsheets]

Wow that’s such lovely feedback. I really am happy I’m able to explain the formulas as DAX is never that simple. I have a few more videos coming up with some DAX issues that are hard to solve so keep an eye out for them!

[@EmileDavis]

Just going to echo the other comments here. I was asked to create a report that does just this. I know how to do this out of the box with excel pivots, but I wasnt sure how to get it done in DAX. Followed your steps, and it worked like a charm. Great explanation of the technique!

[@learnspreadsheets]

Thanks for the kind words! I’m glad it worked. I know right! It’s so easy with excel pivots but so hard with DAX for some reason!

[@JingXiu-vw1gb]

thank you so much! been scouring the internet forever and this is the best!

[@learnspreadsheets]

Oh wow! That’s so kind 😃 glad you like it!

[@GraceMichaels]

Very very helpful and thorough

[@learnspreadsheets]

Thanks for the comment!

[@yuanzhang9487]

Great video! I want to have a trend chart showing percentage over period. I created a measure of subcategory as period so it works in the matric view I can see each period what the percentage is for certain categorybut I can't manage to get the numbers in trend chart, I was hoping put xis period will work... Do you have any idea how to make it work? Thanks!

[@learnspreadsheets]

I’m glad you like it! Sorry but I can’t think of a way to make it work that way

[@ramadanaltaip]

Thank you its huge help 🤩

[@learnspreadsheets]

No worries! 😃

[@gabymichelli1488]

Hello, that's very helpful! Is there a way I can make that as a card for example to show the percentage of an specific category only? When I do it it only shows "100%" as the response. Thanks!

[@learnspreadsheets]

Good question! The new card visual (which just got released & has to be turned on in options > preview settings) has more capabilities than the original card visual. If you add visual level filters it should help with what you’re trying to do but I cannot know for sure. Hope that helps

[@AlexandrePena-z2y]

Perfect!!! Thank you

[@learnspreadsheets]

Glad you like it

[@christianjavier1648]

amazing....! thanks so much!

[@learnspreadsheets]

Glad you like it!

[@MsIzzat92]

Hi, this was really helpful for my end. Just wondering if I would like to concat the subcategory percentage and the monarch, how would the measure looks like? Thank you!

[@learnspreadsheets]

Hey! Great question. I have another video on concatenating tricks that can be combined with this one

[@ChrisWest-v4i]

What is the measure [Years on throne]? Also, what is the symbol next to Monarch in the table? I'm getting 100% for every result using the measure % Of Total Subcategory but I'm guessing what Years on throne is so may have something wrong there.

[@learnspreadsheets]

Did you try downloading my sample file? That should help, link is in the description

[@prathimakumar1119]

@@learnspreadsheets hi couldnt find the measure [Years on throne] , can you provide the DAX here?

[@pepper_lab]

Great 🎉🎉🎉🎉thanks

[@learnspreadsheets]

Glad it’s useful!

[@taminh1781]

how can i do that with parameter? there are many types of field we want to caculate % of parant total. example i have 3 columns Department, Team, Sub-Team. i use parameter call "Team parameter" to select which field to show between: Team or Sub-Team and now how can i create table with Department 100% and Team or Sub-team show percent of parent Department base on parameter "Team parameter"

[@learnspreadsheets]

Hi thanks for the question. It’s difficult to answer here I’m afraid, I’d recommend finding a power bi forum where you can upload pictures & a sample file even, to get a good reply