Excel Magic Trick 1395: DAX Measures for Show Values As % of Grand Total, Column Total, Parent Total

See how to simulate the Standard PivotTable Show Values As calculations of % of Grand Total, % of Column Total and % of Parent Total. See the DAX Functions: CALCULATE, ALL, ALLSELECTED, VALUES, DIVIDE, BLANK, ISFILTERED, and IF. The DAX Measures demonstrated will work in Excel Power Pivot or Power BI Data Models.

1. (00:04) Download Files
2. (00:14) Introduction
3. (01:50) look at our source data tables and relationships in the Excel Power Pivot Data Model
4. (03:52) Brief Discussion about Relations between table and how they help DAX Formulas and the Data Model to calculate quickly on big data.
5. (05:54) Look at the DAX Calculated Column and DAX Measure that are already in the Data Model and build a PivotTable to demonstrate “Filter Context” and look at how it will influence the DAX that we will create.
6. (10:00) % of Grand Total: using ALL, CALCULATE and DIVIDE DAX Functions.
7. (17:20) % of Grand Total Set by Slicer: using ALLSELECTED, CALCULATE and DIVIDE DAX Functions.
8. (20:32) % Column Total: using ALLSELECTED, CALCULATE, VALUES and DIVIDE DAX Functions.
9. (26:22) % Parent Total using: ALLSELECTED, CALCULATE, VALUES, DIVIDE, IF, ISFILTERED and BLANK DAX Functions.
10. (33:28) Summary

Reference Video:
Excel Magic Trick 1394: Excel Formulas for: % Column Total, % Parent Total, % Parent Row Total
Excel Magic Trick 1393: PivotTable Show Values As: % Column Total, % Parent Total, % Parent Row Total
Excel Magic Trick 1395: DAX Formulas for % Grand Total, % Column Total, % Parent Total

Search Phrase Keys: How to create Show Values As Calculations in Excel Power Pivot. How to create Show Values As Calculations in DAX. How to create Show Values As Calculations in Power BI Desktop. How to create Show Values As % Column Total Calculations in Excel Power Pivot. How to create Show Values As % Column Total Calculations in Power BI Desktop. How to create Show Values As % Column Total Calculations with DAX Expression. How to create Show Values As % Parent Total Calculations in Excel Power Pivot. How to create Show Values As % Parent Total Calculations in Power BI Desktop. How to create Show Values As % Parent Total Calculations with DAX Expression. How to create Show Values As % Grand Total Calculations in Excel Power Pivot. How to create Show Values As % Grand Total Calculations in Power BI Desktop. How to create Show Values As % Grand Total Calculations with DAX Expression.

[Al-Ahdal]

In any continent you are Mike is a Global Excel Maestro (irrespective of geographical boundaries). God Bless you.

[u]

Off all the videos I have seen on Excel and DAX over the years, your videos and explanations are by far the best and easiest to understand. Other Dax videos seem particularly confusing (even the paid ones) - they know their stuff but fall at the last hurdle when explaining. Thank you very much for your videos.

[anasfrh]

Came here to say god bless you!! I spent countless hours browsing videos and reading blog posts and forum answers trying to find how to calculate subtotals and you're the only person who actually showed how to do it! Thank you!!!

[HoangNguyen-tbdk]

I'm gonna share these videos to all of my friend. you are the best teacher of Excel that I have ever known. From Vietnam

[leesmx]

Mike is the man! Best Excel Instruction videos I have seen by far! Another great video!

[dariomartinezb]

I'm glad I found your video. The % by Column Total was a the thing I was looking for. Using VALUES() in this way made my PowerBI DAX function work like I needed. Thanks!!!!

[johnborg]

It may sound crazy to many I took 1 Day leave, watching the videos Mike grouped for me. Thanks Mike : )

[mohamedchakroun]

Thanks a lot Mike, I am stlill agree that you are the GOAT (Greatest Of All Time), we learn much more others tricks when hearing you explaining a feature a mesure a function and so on

[shoeshines]

I appreciate the clear breakdown of the DAX functions, especially the distinction between ALL and ALLSELECTED. There was a another great example at the end with integrating the VALUES function to see a specific row or column filter. This is a great distinction with the DAX explicit measures, Great video!

[LeilaGharani]

Thank you for these 4 examples and really nice and simple way of explaining the DAX formulas.

[Victor-ollo]

That video shows the magic power of DAX. Thanks Mike for the excellent video !!!

[davida.taylor]

This is very helpful to see DAX measures in use bc while I've been learning and using them, some are still confusing. Videos like these are helping to clear the fog of war.

[niavras]

Amazing work. Can't be better way to transfer this knowledge! Thank you Mike!

[melissaobrien]

Amazing! thank you so much for such a fantastic explanation

[sgomezrbcn]

Thank you very much for your helpful tutorials, they are very useful and easy to replicate.

[pravinshingadia]

Great video Mike - love these Magic Tricks! This helped me solve a problem today.

[MySpreadsheetLab]

Thank you so much Mike for the crystal clear explanation!!! 😀 i fully understand it now 🤓

[himanshudalai]

This is one more fantastico lesson. Thank you Mike !!

[deepakagrawal]

Excellent video, Mike. Thanks a ton! 👌👍

[MaanEid]

Thanks a lot! explanation is AMAZING!!