DAX - Percent of Total vs Percent of Grand Total
ฝัง
- เผยแพร่เมื่อ 22 ก.ค. 2024
- In this video Mitchell discusses the difference of writing a Percent of Total calculation in DAX vs. using the default built-in capability that will return the Percent of Grand Total.
👍 If you enjoy this video and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Mitchell30" at check out when purchasing our On-Demand Learning classes to get an extra 30% off - pragmaticworks.com/pricing/
00:00 - Introduction to DAX
00:38 - Building a Custom DAX Measure for Percent of Total
01:10 - Modifying Filter Context for Total Sales Measure
02:10 - Creating a Variable to Capture Total Sales for All Countries
03:23 - Verifying Total Sales for All Countries with a New Measure
05:20 - Performing Division Calculation for Percent of Total Sales
06:23 - Using Built-In Power BI Functionality for Percent of Grand Total
07:30 - Comparing Results and Understanding the Difference between Percent of Total and Percent of Grand Total
8:30 - Conclusion Remarks
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:
👉 On-Demand Learning - Start With The FREE Community Plan: tinyurl.com/2ujujsb5
🔗Pragmatic Works On-Demand Learning Packages: pragmaticworks.com/pricing/
🔗Pragmatic Works Boot Camps: pragmaticworks.com/boot-camps/
🔗Pragmatic Works Hackathons: pragmaticworks.com/private-tr...
🔗Pragmatic Works Virtual Mentoring: pragmaticworks.com/virtual-me...
🔗Pragmatic Works Enterprise Private Training: pragmaticworks.com/private-tr...
🔗Pragmatic Works Blog: blog.pragmaticworks.com/
Let's connect:
✔️Twitter: / pragmaticworks
✔️Facebook: / pragmaticworks
✔️Instagram: / pragmatic.works
✔️LinkedIn: / pragmaticworks
✔️TH-cam: / pragmaticworks
Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: (904) 638-5743
Email: training@pragmaticworks.com
#PragmaticWorks
Hey buddy, seriously, you saved me from a total meltdown! Can't thank you enough for coming through when I was struggling. Bless you for real!
Thank you!!! Very simple explanation, I was for hours looking for this answer!!
Clear and concise as always. Thanks a lot Mitchel!!
hurray, thanks for helping me solve this problem! Feeling like a little coder over here now.
Great job, Mitchell. Quick and Concise
Super clear more clear than spanish Videos, thx man, you are a master
Mitchell is the master. Always well explained
Thank you. I'm finally starting to understand this one :D
This was very helpful. Thank you!!
Very very helpful and thorough
Very helpful, Thank you
Should have watched this hours ago!! Thank you! Also, where is the video you mentioned to writing a DAX function that does the same calculation?
Thanks for this video very useful where is second part of video
Hi, did you ever make a prequel video as discussed at the end of this video? I need to create grand total in DAX to add them together for a total ratio formula
Can I ask what the formula of your Total Sales measure is? Trying this out with my data (happens to be Total FY Actuals and Projects rather than Sales and Countries) is just giving me the same values as the column before it when trying to validate in the first step, rather than the same sum value in all rows.
Great stuff! Would this work as well if you add years to the occasion? % of total per country and per year that is!?
Update - I used REMOVEFILTERS to make it work. Not sure why but it does over the ALL function
Can someone help? to calculate the overall sales my code is correct (all slicers, filters have been removed too) and I'm not getting the repeating overall total sales. It just gives me the total by country as per the 1st column. I can't see any faults, its driving me mental
What’s the video that solves the DAX measure, so it shows the same value as the “Show As” calculation?
Thanks for the explanation!
Yes, I am very interested in too ! Thanks
Awesomes video and easy to follow, I have Income statements in which First row item is total sold quantity(sales volume) and then the Revenue(3 sub Product), Cost of Sale(5 sub line), Gross profit, Direct cost element(10 sub line), operating expenses(sub line 10) then Net profit, I need to divided all Main and subline item with sales volume placed on first row in the table to figure out the Rate per unit, This is the income model used in Readymix Concrete industry as i have 10 location, i need to do this for every location, Since you are Expert could you please help me in writing Measure, Thanking you in advance
excellent
That grand total percent which will follow the currently selected countries require Allexcept instead of All I think.
why dont I see that option to show as percent of grandtotal in new version of power bi?
I face the same problem with you
can you give me dax code for %GT Total Sales, which calculates selected values share?
instead of all() you can use all selected, or use filter where value column in { selected valuesfrom a slicer }
Thank you. You mentioned that you could resolve the blank that was shown in the table. Do you have a video?
The challenge that I have is when I also have to include RLS to the mix. RLS destroyed my denominator of the pcttototal.
when i do the same i get the same sums that are in collumn "Total sales", not the actual total sum. I wasted so much time on this it is getting frustrating
what is [total sales]
where the part 2 of this video? regarding grand total %
Just use Allselected() instead of All()
What I run into constantly is, how do you get % of grand total ONLY for the grand total section. If I have 5 years of data in the matrix, I do not want the # to show up there. I know you can squish the value to be skinny, but when you have 4 grand total calcs, it cosmetically starts to get crazy as you have to turn off wrap and all kinds of other stuff. For example, one report, 5 years as columns, with ONLY adding % of grand total to the very end. I can add it to the end, but cannot stop it from showing up on each year also, which I do not want. This has been forcing me to give up on Power BI and make a view in a flat table and just do all my logic in Sql in the database. When dax cant do 100% of what I need, its worthless and I am constantly NOT using it because it doesnt get you there.