Understanding CALCULATE in DAX: Percentage of Total
HTML-код
- Опубликовано: 27 июл 2024
- This video shows different ways for calculating the percentage of a total in DAX. Using different methods you can manipulate the evaluation context using CALCULATE.
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
SUPPORT MY CHANNEL
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ruclips.net/user/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#DAX #CALCULATE #BIGorilla
This is beyond helpful. Was totally struggling on the Dax syntax for this. Super greatful. Especially for the part where the column will change for a new or 2nd pivot.
I’ve looked everywhere on the internet to find a solid way to calculate the percentage of total row, and this is exactly what I was looking for.
Thank you for the well explained video!
Thank you so very much! I spent all day yesterday trying to get this exact work done. I'm grateful
finallyy man.. i been at work all day trying to find an example of calculating a weighted average... your a lifesaver and earned yourself a new subscriber!! 100%
great explanation , 4 days searching for this solution, Many Thanks ❤❤👌👌
Thank you. Exactly what I have been looking for. To calculate the % of subtotals.
very helpful. Thank you.
Brilliant video, thank you so much. By far the best explaination of this I've seen. Thank you for taking the time to make and share.
This was exactly what I was searching for. Thank you!
Perfectly explained
This is fantastic, gold star for me when I use this in my report. Many thanks
Happy to hear that Terry 😁
This is a great tutorial to create total percentages in DAX. I have one question I'd would like your help. Let's say I am creating a matrix visual about some population distribution across states. I also break down the states' distribution by demographics such as age group, gender, race group (in the matrix, state is the row field, and gender, or age group, or race are the columns). To show the row percentages for different categories of age group, gender, or race group, I would like to create a DAX measure; the row total disregarding gender should be:
VAR population_rowtotal=
CALCULATE(
[# population],
REMOVEFILTERS(pop_final[gender]
)
)
I can calculate the row percentages as DIVIDE([# population], [population_rowtotal]).
This DAX measure is for row percentages for gender only. I can also create DAX measures for row percentages of race, or age group.
But I would like to use a parameter including gender, race, age groups, as the column in the matrix. So the matrix will be dynamic based on the column field I select. That means the Dax measure of row total / percentage should also be dynamic based on the selected column field. How can I achieve this in the DAX measure statement:
REMOVEFILTERS(pop_final[gender] or [racegroup] or [agegroup]).
Thanks.
Nicely explained!
You are the best☺️👍
Hi there wonderful video thank you so much! Quick question, what application do you use to Box (red) around a certain area while demo'ing something? I have Zoomit, which is helpful with Zooming but not very easy to get to a box.
Nice presented.
Thanks Emir, hope it was helpful!
Very useful video
Thank you !!
Hi Rick, this is really good and helpful. I had a question. What if I want my row total to be treated as 100% and then each cell of that row reflects the % contribution leading to the row total.
Is this possible. How will the DAX work in this scenario.
Any help or idea is appreciated.
Thanks a lot.
Regards.
😊
Heel gaaf. Bedankt
Bedankt Rowan, leuk dat je kijkt!
Hi, well I'm an 'All ' person, simply because I can use it in Bi or PP. My question is this, if removing all filter to create the denominator, i've used
CALCULATE( [Total sales], All(CALENDAR ) , ALL(Sales Table) ) , as I don't always know what I'm going to be dropping in to the visual, usually matrix, I can't help feeling there should be a better approach, I've tried using summarize and crossjoin, any suggestions on removing filters from multiple tables
What is the difference between "Return variable name" and "Return Result"? Thank you!
Hi, a question, you didnt show how the [ sales ] measure was created, do you mind explaining?
Hi Anthony,
The sales measure is a SUM function of the amount column. I didn't go into depth because it's not the focus of the video. And you can apply the instructions on many different measures.
Just for completeness, you may have a table that's called 'Sales' with a column named 'Amount'. The measure could then be:
Sales = SUM( Sales[Amount] ).
Does that help?
Rick ^^
@@BIGorilla Thanks alot yes it does. Appreciate the response, and great video!!
Hi Gorilla,
Great thanks for this Clips. It is very informative. I had one query, whether the figures will get change as per filter selection. Thanks in Advance
Hi Shan,
The answer is, it depends. Your numbers will generally change with the filters you apply. However, if one of the formula's takes away the filters from a column or table using either ALL() or REMOVEFILTERS() it may be that the numbers are not affected by the slicer.
If that's not the desired behavior, you can adjust your formulas to use ALLSELECTED() instead of ALL() or REMOVEFILTERS()
Hi Gorilla,
Thanks for your response. It has worked out.
🙏
what happened if we need the share of the productos? I mean that the sum of % products to be 100%
for example: Class deluxe: 16%
A.Datum Super :57%
A.Datum Constumer: 43%
I’m trying to use this video to calculate the percentage of subtotals - so to use the data from your example 4 is 13% of 31 (the total for regular).
Hi Claire,
That's a good exercise. When you try to calculate the % of the subtotal, it's important to know which filters to keep and which to ignore. The hierarchy in the model shows two levels. Level 1 = Class, level 2 = Productname. To calculate the % of the total of the subcategory in Level 1 (class), you remove the filters on level 2 (productname).
VAR Sales = [Regular Sales]
VAR Subtotal = CALCULATE( [Regular Sales], ALL( DimProduct[ProductName] ) )
VAR Result = DIVIDE( Sales, Subtotal )
RETURN Result
Before code should give you the results you're looking for. The part that says ALL( DimProduct[ProductName] ), removes the filters from the field ProductName. SInce the field Class is still in there, it respects the filter on class. And you get your desired result.
Hope this helps!
great video..can we follow similar process when calculating margin% yearly
Hi Vamshidhar,
Absolutely. You can do something similar. You may need to adjust the filter context so it looks at the different years. Difficult to say without an example though.
Enjoy DAX!
@@BIGorilla thank you for your reply..
Hi
Hope you are doing
I like your way of explanation for every topic
very useful video and very practical need everyone for every project ......very big thanks to share with us.
If it can possible can you plzz provide us excel data file which you used while we can do practice on same data either you can provide us pbix file.
Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
Hey Ashish, thanks for the suggestion. At this point I don't have the file available yet. I may provide this in future videos though.
Cheers, Rick
Hi Sir, facing an issue need your help for the mentioned below
I have a Project with 3 Location columns and 1 value columns (WTG = 100% sum of all location activities) in power Bi. I want to see percentage in the Card visual chart and it should be link with locations. means when I select location or its sub location so it show me cumulative percentage of that selected location only calculating it as 100%.
Construction = 100%
Pipeline = 45%
Wellheads = 30%
Processing Plant = 25%
I want like this if i select construction the Card visual show me percentage from 100% and when i select its sub-phases e.g. pipeline then it should not show me 45%; it should show me 100% so that i can change the date and calculate the variance in one visual.
If i need % for product name from class name and class name from grand total ??????
You can do something like:
VAR Sales = [Regular Sales]
VAR Subtotal = CALCULATE( [Regular Sales], ALL( DimProduct[ProductName] ) )
VAR Total = CALCULATE( [Regular Sales], ALL( DimProduct[ProductName] , DimProduct[Class] ) )
VAR Result =
SWITCH( TRUE(),
ISINSCOPE( DimProduct[ProductName] ), DIVIDE( Sales, Subtotal )
ISINSCOPE( DimProduct[Class] ), DIVIDE( Sales, Total),
DIVIDE( Sales, Sales)
)
RETURN Result
Hope that helps!
Rick
5:44 Je deelt toch niet door nul? Je deelt dan nul door 31 en dat geeft weer nul en wordt dan niet getoond.
Klopt, scherp gezien. Ik zei in de video division by zero, maar het was eigenlijk 0 gedeeld door een getal.
Beide zorgen ervoor dat het resultaat nul is en niet wordt getoond. Thanks voor de opmerkzaamheid!
Rick
I’ve looked everywhere on the internet to find a solid way to calculate the percentage of total row, and this is exactly what I was looking for.
Thank you for the well explained video!