What if Revenue is equal of two categories in your case Product Type. Cumulative Total will not give correct result if revenue will be equal. Filter function will iterate and will capture 2 or more product type having same revenue while summing the revenue variable. Do you have solution to this problem???
You need to create an additional table (say ranking table with columns product and revenue) and calculate the rank based on two fields. Rank the Product Types as well first. product Rank = Rankx(All(RankingTable),[revenue]) Then create a column Composite rank = var __max = Max(rankingTable[product rank]) return rankx( all(rankingTable), [revenue]* __max + rankingTable[product rank]) then use this ranking table in your pareto measure. note: I have typep this using my phone, so might be few syntax error
My "revenue" parameter comes up as a being "parameter not correct type". Any ideas. Lost a few hours on this now! My table name has '' around it I wonder if that is the problem? Total_spend is a numerical variable. Pareto curve = VAR RunningTotalSpend = CALCULATE(SUM('Table1 (2)'[TOTAL_SPEND]),ALLSELECTED('Table1 (2)'[IC11_DESC1])) VAR CurrentSpend = sum('Table1 (2)'[TOTAL_SPEND]) VAR SummarizedTable = SUMMARIZE( ALLSELECTED('Table1 (2)','Table1 (2)'[IC11_DESC1],"Revenue",SUM('Table1 (2)'[TOTAL_SPEND]))) Any guidance much appreciated.
is the formula effective? let say I have these numbers: 1000,500,500,500,500,500,500 (total = 4000) in your pareto measure everything except the 1000 value will be at 100%. we should have a running total formula in place (and some order in place based on the $ then the product type)
I have this same issue. When you have duplicate values, whatever the total would be for the last duplicate becomes the value for all the duplicates using this measure, which is not correct.
Hi, yes this would cause an issue for this formula. If you wanted to get around this, you can add a column to the SUMMARIZE clause that would rank the data based on the value and a specific order that you specify. You can then use this ranking when returning the final Pareto %. I don't have the exact code to accomplish this (since I've never done it) but I'm sure that this would be possible.
Hi @@BIElite , I ended up solving it this way. I imagine it is not the most elegant, but it works. www.linkedin.com/pulse/valores-id%C3%A9nticos-en-un-gr%C3%A1fico-de-pareto-isidre-bagu%C3%A9-s%C3%A1nchez?articleId=6722864315512209408#comments-6722864315512209408&trk=public_profile_article_view
Ty, great vid! Thanks to you, I was able to do the Math in one measure. My only Nemesis are the negative numbers. I dont know if my Math is bad or if the pareto dont work with negative numbers :D like what if the revenue is minus.
Hi, I have noticed bug here. When there are 2 or more same high columns then pareto line do not sum cumulate values from these same columns... Do You have it also ? I do not know how to fix it...Can You help me?
Thank you so much for this tutotrial. This is a different method than most of other explanations through the internet. Fortunately, this method worked for me, while others failed. In my case I had to do a cumulative sum of averages (calculated as a measure), and your way of creating a sumarized table, worked very well.
Here I am, finding this video over 2 years later. Pausing to write you a thank you for posting this. Was fighting an issue that with my pareto that I just couldn't get to work right. Followed along with this video and found my error. Plus a little nugget about the measure being specific to the horizontal axis of choice. Thanks Parker and Reid.
Hi, I hope you are well, I just want to say thank you, I have been struggling with this for the longest time, I highly appreciate you spending your time to create this video.
First of all thanks for the guide. There's one thing that doesn't seem to work, and that's when two values have the exact same quantity. The first value is not added to the cumulative, the second one is added twice. For example A = 2, B = 1, C = 1, will result in a pareto that looks like 50%, 50%, 100% instead of 50%, 75%, 100%. Any way around this?
Great video. I have only an issue that on x-axis (type = continuous) I have negative and positive values and pareto line for negative values decreases from 100% to 0% and for positive values it increases from 0% to 100%. In fact i have a pareto line which looks like "V" shape. What should I do that it look like a proper pareto line...
Generally pareto's work best with positive values only. One idea is that you could convert everything to an absolute value using ABS in DAX, but just for the pareto line, still display the original value on the chart as negative
Thank you. Both Parker Stevens and I provide training courses. Parker's are located here training.bielite.com/ and mine are here www.havensconsulting.net/online-courses
A little confused about the logic [Revenue] >= CurrentRevenue. What if the summarize table is not desc ordered, or even not ordered at all? Can we control the order of the summarize table? Thanks.
Hi, I would like to ask a question. With the addition of the conditional formatting, how would you add a legend to show the names between the vital few and significant many?
Unfortunately there's no native way to add a legend like that. Unless you used a custom visual like the HTML5 Content Viewer or similar, and made your own SVG rendered legend. But that would need to be "coded" from scratch basically :\
Hi Jenifer, I can't tell you exactly how to speed it up as this is the best solution that I've come up with. I bet that some of the DAX gods like the people over at SQLBI could speed it up a bit. Maybe check out some of their DAX optimization techniques? www.sqlbi.com/topics/optimization/
it's great! but I have some question. How to creating pareto if column that you want to filter in same table of value Ex. if product column in sales table. Can you help me? please
how do i group it dynamically by diferent levels of hierarchy(diferent columns)?? And dinamically changing by the selected levels/items? Still, you saved my life man! Now, my pareto's are working better! thanks!
Excellent step by step deconstruction and buildup of the process to create the measure. Great explanation on general work flow to develop more complex formulas. Thank you very much!!
Hi, any reason why my total revenue and my currrent revenue are comign out the same? I get a horizontal line when i return C/T or T/C Pareto % = VAR TotalRevenue = CALCULATE(SUM(apsq[Sales Revenue]),ALLSELECTED(apsq[Sales Revenue])) VAR CurrentRevenue = Sum(apsq[Sales Revenue]) return TotalRevenue/CurrentRevenue
i do the same calculation on my model but some value shows no value that not make the effect what is the wrong on the following equation (instead of product in your example i put sales by site) (sales 2020 = expression ) PARETO% = VAR TotalRevenue = CALCULATE(Sales[2020 sales],ALLSELECTED('Sales')) VAR CurrentRevenue = Sales[2020 sales] VAR SummarizedTable = SUMMARIZE( ALLSELECTED(Sales), Sales[Site_1], "Revenue",Sales[2020 sales] ) VAR CumulativeSum = SUMX( FILTER(SummarizedTable,[Revenue] >= CurrentRevenue), [Revenue] ) RETURN DIVIDE(CumulativeSum,TotalRevenue)
very instructive video on pareto and DAX, well explained how the measure works, Is there a Pbix file from this video where I can test with thanks in advance Roger ( new to this channel )
Thank you so much for this video! I was hoping to find some Pareto Model but dynamic like this one. I just have a quick question. I see that you kinda use just one measure that groups every other measures to find the Pareto line. But I would like to know if there's a chance to use that same measure or another one to maybe count how many products we have per country and how many of them are Pareto. I'm trying to figure out this because I have a report with like a thousand products and I wanna use maybe some cards so people can see actually how many products of total are Pareto. Thank you so much again.
are you able to resolve this issue as i am also struggling with the same as i have 29K customer so i am getting correct result with measure but when i select multiple months it shows result after may be 50 seconds or sometime i received visual exceeds resources error.
What if Revenue is equal of two categories in your case Product Type. Cumulative Total will not give correct result if revenue will be equal. Filter function will iterate and will capture 2 or more product type having same revenue while summing the revenue variable. Do you have solution to this problem???
I am facing the exact same issue. Please let me know if there's a solution.
How do you handle exact same values? Like if the Revenue is exactly the same.
I also want to know this!!
You need to create an additional table (say ranking table with columns product and revenue) and calculate the rank based on two fields.
Rank the Product Types as well first.
product Rank = Rankx(All(RankingTable),[revenue])
Then create a column
Composite rank =
var __max = Max(rankingTable[product rank])
return
rankx( all(rankingTable),
[revenue]* __max + rankingTable[product rank])
then use this ranking table in your pareto measure.
note: I have typep this using my phone, so might be few syntax error
Interesting approach to the Pareto situation!
Good tip on minute 05:19 to show the results
of the summarized table
My "revenue" parameter comes up as a being "parameter not correct type". Any ideas. Lost a few hours on this now!
My table name has '' around it I wonder if that is the problem?
Total_spend is a numerical variable.
Pareto curve =
VAR RunningTotalSpend = CALCULATE(SUM('Table1 (2)'[TOTAL_SPEND]),ALLSELECTED('Table1 (2)'[IC11_DESC1]))
VAR CurrentSpend = sum('Table1 (2)'[TOTAL_SPEND])
VAR SummarizedTable =
SUMMARIZE(
ALLSELECTED('Table1 (2)','Table1 (2)'[IC11_DESC1],"Revenue",SUM('Table1 (2)'[TOTAL_SPEND])))
Any guidance much appreciated.
is the formula effective?
let say I have these numbers:
1000,500,500,500,500,500,500 (total = 4000)
in your pareto measure everything except the 1000 value will be at 100%.
we should have a running total formula in place (and some order in place based on the $ then the product type)
I have this same issue. When you have duplicate values, whatever the total would be for the last duplicate becomes the value for all the duplicates using this measure, which is not correct.
Hi, yes this would cause an issue for this formula. If you wanted to get around this, you can add a column to the SUMMARIZE clause that would rank the data based on the value and a specific order that you specify. You can then use this ranking when returning the final Pareto %. I don't have the exact code to accomplish this (since I've never done it) but I'm sure that this would be possible.
Hi @@BIElite , I ended up solving it this way. I imagine it is not the most elegant, but it works.
www.linkedin.com/pulse/valores-id%C3%A9nticos-en-un-gr%C3%A1fico-de-pareto-isidre-bagu%C3%A9-s%C3%A1nchez?articleId=6722864315512209408#comments-6722864315512209408&trk=public_profile_article_view
Yes , I have the same problem related to any duplicate value ?? i add a additional table , but it isn't working 🤔🤔
Ty, great vid! Thanks to you, I was able to do the Math in one measure. My only Nemesis are the negative numbers. I dont know if my Math is bad or if the pareto dont work with negative numbers :D like what if the revenue is minus.
Would this work with a slicer for time as well ? Like, if I wanted to see the top 20% customers based on revenue from a particular month
Hi, I have noticed bug here. When there are 2 or more same high columns then pareto line do not sum cumulate values from these same columns... Do You have it also ? I do not know how to fix it...Can You help me?
Why no one is showing a video on how to do a Pareto when you a duplicated values argggggggh
Thank you so much for this tutotrial.
This is a different method than most of other explanations through the internet.
Fortunately, this method worked for me, while others failed.
In my case I had to do a cumulative sum of averages (calculated as a measure), and your way of creating a sumarized table, worked very well.
Here I am, finding this video over 2 years later. Pausing to write you a thank you for posting this. Was fighting an issue that with my pareto that I just couldn't get to work right. Followed along with this video and found my error. Plus a little nugget about the measure being specific to the horizontal axis of choice. Thanks Parker and Reid.
Mate I saw a few videos on the topic, but your was the easier to follow and with the simplest calculation. Thank you
You're welcome!
Wow! Great feature that I can use today! Thanks!
Thanks! Really a good method, logical and easy to follow with no hidden measure.
Hi, I hope you are well,
I just want to say thank you, I have been struggling with this for the longest time, I highly appreciate you spending your time to create this video.
Glad I could help!
Greates method for cumulative total bar chart and SELECTEDVALUE , thankssss😂😂
You're welcome!
First of all thanks for the guide.
There's one thing that doesn't seem to work, and that's when two values have the exact same quantity.
The first value is not added to the cumulative, the second one is added twice.
For example A = 2, B = 1, C = 1, will result in a pareto that looks like 50%, 50%, 100% instead of 50%, 75%, 100%.
Any way around this?
I have the same problem, have you found a way around this?
@@christianjohansson9944 not yet, dealing with big sample sizes so it doesn't come up very often. Haven't looked into it any further.
@@stobbie2415 Ok too bad, I will keep using R then. It would be nice to have the interactive Power BI experience for my Pareto-charts.
Having the same issue.
I’m having the same issue. Is that possible if Havens Consulting can advise us on this
Great video. I have only an issue that on x-axis (type = continuous) I have negative and positive values and pareto line for negative values decreases from 100% to 0% and for positive values it increases from 0% to 100%. In fact i have a pareto line which looks like "V" shape. What should I do that it look like a proper pareto line...
Generally pareto's work best with positive values only. One idea is that you could convert everything to an absolute value using ABS in DAX, but just for the pareto line, still display the original value on the chart as negative
Superb explanation! Do you have a course which can teach us to learn these advanced measures/visuals?
Thank you. Both Parker Stevens and I provide training courses. Parker's are located here training.bielite.com/ and mine are here www.havensconsulting.net/online-courses
A little confused about the logic [Revenue] >= CurrentRevenue. What if the summarize table is not desc ordered, or even not ordered at all? Can we control the order of the summarize table? Thanks.
I don't think you can control the order of a summarized table but you can create a ranking column that you can use in later formulas.
Hi, I would like to ask a question. With the addition of the conditional formatting, how would you add a legend to show the names between the vital few and significant many?
Unfortunately there's no native way to add a legend like that. Unless you used a custom visual like the HTML5 Content Viewer or similar, and made your own SVG rendered legend. But that would need to be "coded" from scratch basically :\
Damn,this is epic!!!
Great video but the report is too slow for large dataset. what do i do?
Hi Jenifer, I can't tell you exactly how to speed it up as this is the best solution that I've come up with. I bet that some of the DAX gods like the people over at SQLBI could speed it up a bit. Maybe check out some of their DAX optimization techniques? www.sqlbi.com/topics/optimization/
it's great! but I have some question. How to creating pareto if column that you want to filter in same table of value Ex. if product column in sales table. Can you help me? please
how do i group it dynamically by diferent levels of hierarchy(diferent columns)?? And dinamically changing by the selected levels/items? Still, you saved my life man! Now, my pareto's are working better! thanks!
Awesome tutorial! Simple, on point, and beginners-friendly. Thank you so much for this!
Incredibly helpful video! Thank you!
Glad it was helpful!
Simply WOW. Open mind solution
Thanks
For whatever reason I don't have the advanced controls option when trying to set the conditional formatting, anyone know why that might be?
Thank you very much! I hope that God bless your day, you helped me a lot!
Wow I liked the tip on creating a table to see it. I always do it in my brain and often get lost and had to write on iPad
Thank you soooo much! Helped me a lot in my project!
Excellent Informative Session
Thanks a lot
Excellent step by step deconstruction and buildup of the process to create the measure. Great explanation on general work flow to develop more complex formulas. Thank you very much!!
Hi, any reason why my total revenue and my currrent revenue are comign out the same? I get a horizontal line when i return C/T or T/C
Pareto % =
VAR TotalRevenue = CALCULATE(SUM(apsq[Sales Revenue]),ALLSELECTED(apsq[Sales Revenue]))
VAR CurrentRevenue = Sum(apsq[Sales Revenue])
return TotalRevenue/CurrentRevenue
NVM i had to ALLSELECT(dataset)
i do the same calculation on my model but some value shows no value that not make the effect what is the wrong on the following equation (instead of product in your example i put sales by site)
(sales 2020 = expression )
PARETO% =
VAR TotalRevenue = CALCULATE(Sales[2020 sales],ALLSELECTED('Sales'))
VAR CurrentRevenue = Sales[2020 sales]
VAR SummarizedTable =
SUMMARIZE(
ALLSELECTED(Sales),
Sales[Site_1],
"Revenue",Sales[2020 sales]
)
VAR CumulativeSum =
SUMX(
FILTER(SummarizedTable,[Revenue] >= CurrentRevenue),
[Revenue]
)
RETURN
DIVIDE(CumulativeSum,TotalRevenue)
I experience the same problem now. did you find any solution?
Very useful !!! Thanks a lot !
Thank you! This is super helpful!
very instructive video on pareto and DAX, well explained how the measure works, Is there a Pbix file from this video where I can test with thanks in advance Roger ( new to this channel )
Sure thing. There's a link to my Blog Files page in the description if you're an email subscriber. Link in there to sign up as well :)
Great demo. Worked perfectly
That is great! Thanks.
Fantastic demo. thanks Parker!
Very thorough explanation! Really thanks
Glad it was helpful!
Beautiful!
Спс!!! Ваще норм 👍👍👍
Thank you so much for this video! I was hoping to find some Pareto Model but dynamic like this one.
I just have a quick question. I see that you kinda use just one measure that groups every other measures to find the Pareto line. But I would like to know if there's a chance to use that same measure or another one to maybe count how many products we have per country and how many of them are Pareto. I'm trying to figure out this because I have a report with like a thousand products and I wanna use maybe some cards so people can see actually how many products of total are Pareto. Thank you so much again.
are you able to resolve this issue as i am also struggling with the same as i have 29K customer so i am getting correct result with measure but when i select multiple months it shows result after may be 50 seconds or sometime i received visual exceeds resources error.
with more data i dont think this will work as power bi takes lot of time to process the vtable with lot of customers
Really useful. Thanks.
awesome great work
Thanks
Nicely explained.
It is Amazing! Thanks
Glad you like it!
That is an awesome one, thank you!!! Any chance to share the pbix?
Absolutely! I just added the link in the video description. :)
Yes … this really is awesome --- I just added it to one of my reports. Thank you very much for sharing!!!
Thank you so much!! @@HavensConsulting
Impresionante! Uno de los mejores definitivamente. Muy bien explicado, muchas gracias!