Calculate Percentage of Total in Power Query
Vložit
- čas přidán 7. 07. 2024
- Computing the percentage of a total is task that even seasoned Power Query users struggle with. Allow me to walk you through the process step by step.
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.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
czcams.com/users/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
Chapters:
00:00 Introduction
00:32 Method 1
02:59 Method 2
04:42 Method 3
05:49 Method 4
#PercentageOfTotal #PowerQuery #BIGorilla
The last part alone, Percent of Subtotal, should get 3 thumbs up! Struggled with some other solutions around the internet but this is by far the best.
Exactly what I need at work. Many thanks for sharing your knowledge. All the best!
Great! After watch this, I can get the initial insight of how my Power BI homework should do
Amazing, great job man! Thank you!
Solved a three year issue with this tricks... eliminated multiple sheets - thanks for sharing
Very helpful. Fourth time watching as I need to use it today. Thank you 🙏 😊
Cool and self-confident style in teaching the important and usefull things related to pBi and pQuery! Thanks Mate!
Love method 3. Thank so much for helpful insights.
Excellent, Very Informative. Thank You.
This is a perfect example that i can use for mothly gross profit margin pct. Thank you.
Thank you very much. This was very helpful. I appreciate it!
Thanks so much. Saved me a lot of time.
Variety of Methods 👌👍 Good to learn
Great! thanks a lot, very helpful!
Wow you are very good at teaching this thing. Thanks!
Awesome, thanks. it solved one of the use case. Cheers.
That's cool ! Thanks for posting!
Thanks Tom, glad you like it!
Thanks very much, nice clear video of how to do this.
Superb! Thanks!
Thanks, this video help me a lot
thanks brother, you r great
U r explanation is very nice and understandable… thank u..
Thank you!!
Great 💯👍
Excellent
thank you so much
Great video, it works well. I do have 1 issue. The measure % value changes if i sort using a sort table added to model or as a new column added using Related function. Is therea wotk around?
Thanks a lot
Thanks 4 sharing 👍👏👏
Thanks Kudi, glad to hear a voice like yours that enjoys it!
Just come back to this topic, if you want a percent of Sub Cat / Cat , and don't want to group the data try this, it's also really useful in lots other ways;
Table.AddColumn(Source, "Percent Sub to Category ", (A)=> List.Sum( Table.SelectRows( Source ,(B)=> B[Sub] = A[Sub] ) [Units] ) /
List.Sum(Source [Units] ) / List.Sum( Table.SelectRows( Source , (C)=> C[Category] = A[Category] )[Units] ) )
Nice one
Great. i got solution
Leuk en leerzaam👍
Bedankt!
what if my previous applied step is not 'source' but 'added conditional column'
That's another helpful trick video. Thanks! A ques: if we wanna see the % of total sales by "Popular" and "Other"? Do I create a custom column and group by category and then calculate the % to total sales?
Ramit - In the video, check out method 4. Your question is answered in that part of the video 😁
@@BIGorilla true, had checked it earlier too. But I was looking for direct solution. Something you did for colours I am trying to achieve for the " Colour Group" Will try few things on my own. But thanks for the the cues.
Good video, but you don't explain why you would want to pick one of these approaches over the others. I am trying to do this and I'm having some crazy performance issues. Is one of these methods faster than the others?
What is “ Expression Error: A cyclic reference was encountered during evaluation”
Can the % of total calculated based on category on each month?
Hi Rick, how about subgroup % of overall? I added an overall total sales columns, , List.Sum, then grouped by Color Group so color group sum sales and second aggregation all rows, so now you have the numerator in the 'outside table and the denominator in the inside, so add columun [Group Total ] / [All Rows] [Overall Total] {0} . it's a bit scrappy, and I'd rather have the calulate the oveall total within the formula bar grouping ? Ho hum,
Hi William,
How about grouping the data by subcategory and adding both column with the 'AllRows' operation, and one with the 'Sum' operation.
Expand the Table Objects created by the AllRows Operation, and divide the Subgroup totals bij the List.Sum( Previousstepname[SalesColumn] )
@@BIGorilla will have a go.
I dont really understand why we calculate percentage of total in power query because it's way much easier to do it in the report part. Did I miss something?
Hey Batman, you’re not missing anything. In principle it’s often better to do this in DAX.
However, as mentioned in the video, there may be cases where you do need this. For example:
- distributing an amount of x number categories
- exporting the result to excel and needing the percentages
- applying a bonus % of an invoice header over each of the invoice lines
There are many more examples and I get this question a lot. So for those in need this should help. And if not for you, then at least you now know the technique 😁
Sorry, not quite with you? on the expansion without having the grouping expanded.
In the mean time, this is almost what I want, as you did version 2,
List.Sum( Source [Sales] ) drill down if necessary save a total
new step Source
Group by Sub category so;
Table.Group(Custom2, {"Color Group"}, {{"Group Sales ", each List.Sum([Sales]), type number}} )
then just add to the grouping so;
Table.Group(Custom2, {"Color Group"}, {{"Group Sales ", each List.Sum([Sales]), type number} ,
{ "Group %" , each List.Sum( [Sales] ) / Total }} )
Now to eliminate the 'Total " step and incorporate that. Hmmm.
William - Without an example file, or the full code (with as source an ‘enter data’ example) I find it difficult to follow. Can you provide one?
@@BIGorilla I've tried sharing the file via onedrive but when I tested the link, I got the file but was ubable to access the queries, they were there but inactive?
@@williamarthur4801 easiest is to use a sample dataset and enter it like this docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop
Then copy paste the advanced editor code with the compressed binary code. It allows to share the query without sharing the file. ☺️
@@BIGorilla Sorry not sure how you mean re sharing sample , I have just updated the shared file, as I've managed to get everything in one step
for a percentage of group sales to overall sales , including changing column type , Grouped by 'Color Group' 'All Rows' ,then nest each bit, fiddly typing everything into formula bar but good when it all works. 😊
🙈 ᵖʳᵒᵐᵒˢᵐ
Soy face in thumbnail on a Power BI tips n tricks video. Mr. Beast has ruined the culture. Why Won't God Blow Up The Sun??