Do mind; this method wont work entirely correct when we would add a filter on the products. The reason for that is that you use ALL. This would cause the cumulative percentage to become too low when filtering. I would recommend to use ALLSELECT. Also, although i like your explanation, i would recommend to also say that rank = rankx(ALLSELECTED(Sales[product]);totalsales) is in fact rank = rankx(ALLSELECTED(Sales[product]);CALCULATE(SUM(Sales[sales]));sum(Sales[sales])) when you write it out, as rankx adds an implicit calculate. Without this, a viewer could think that rank = rankx(ALLSELECTED(Sales[product]);SUM(Sales[sales])) would also be correct :)
Excellent job! I normally don't watch videos like this, but I'm very glad that I did. You can clear, helpful explanations. The step by step walk-through was very useful for a thorough learning experience.
I've checked other videos, however your video is the one that helped me succeed with Pareto's in Power BI. Iwould also be aware of ALL/ALLSELECTED as highlighted by Dennis Priester.Thank you both
Great video! You explained this very well and easy to follow. I appreciate you walking through the functions and explaining how they worked. As I am tweaking this to meet my needs I seem to be having an issue with the "Cumulative Total" formula when there is more than one description column. Example: Column A = Part number; Column B = Product Name; C = Total Sales, D = Rank, etc.... It is not adding up the totals, only duplicating the same number as the "Total Sales" column. In DAX Studio the TOPN([Rank],ALL only brings back the first row. However, with only one "Description" value this works just fine. Thanks again.
Cool! thanks a lot! Question) Is it possible to calculate in the seperate table that in total we had that number of products with acumulative 80% with that sum - like a card view?
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?
Hi I have a question What if in the total sales Column if to product name has same value the rank will also be same for both the product What is the solution for it?
Hi Pradip, thanks for this helpful video. I have 1 question: If there is a case where 2 products have the same "Total Sales" value, the Rank assigned to both of the products is same. In such cases, cumulative sales don't increase for the 2nd product. Do you think this issue can be handled?
Hello, this is very useful, I've managed to apply it to my use case. Unfortunately I do get some rows where values are the same, resulting in duplicate RANK values, which stuffs it up. Any idea as to how to get around it. I need it to be a measure, calculated dynamically. Thanks
First of all thanks for the video. There's one thing that doesn't seem to work, and that's when two values have the exact same sales amount. The first value is not added to the cumulative, the second one is added twice. For example A = 20, B = 10, C = 10, will result in a pareto that looks like 50%, 50%, 100% instead of 50%, 75%, 100% and also the rank is 1,2,2 insted of 1,2,3. Any way around this?
Excellent tutorial! But I have a problem, I have products with exact sales amounts and the fórmula ranks them in the same position (1,2,2,3,4,5), how do I do to rank them in different position (1,2,3,4,5,6) I need this urgently
I found solution, so I'll answer by myself: To avoid problems with duplicates in bar chart you have to introduce there some noise to differentiate duplicate values. For example by adding column with some insignificant random values: New Total Sales= [Total Sales]+(RAND()/10000). Source: radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
![ROSÉ(로제) - APT. (Band VER.) (With. 이영지) [더 시즌즈-이영지의 레인보우] | KBS 241129 방송](http://i.ytimg.com/vi/IW8qaemx74g/mqdefault.jpg)
It is very typical to create Pareto chart but the way you explain is very satisfying, Thank you keep share knowledge make learner life easy.
You rock Pradip! You are a life saver!
Hi, thanks a lot for this tutorial, so far it is the smartest way I found to make an efficient pareto chart in Power BI.
Thank you!
This video is useful. Your explanation is clear and I could understand all function.
Really Explanation is Awesome
Good video and great explanation everyone can understand easily 👏👏👏
Awesome. Very good communication and very good presentation. Very very useful Video.
Thank you very much for your explanation, it help me a lot!!, have a great day💪💪
Do mind; this method wont work entirely correct when we would add a filter on the products.
The reason for that is that you use ALL. This would cause the cumulative percentage to become too low when filtering.
I would recommend to use ALLSELECT.
Also, although i like your explanation, i would recommend to also say that
rank = rankx(ALLSELECTED(Sales[product]);totalsales)
is in fact
rank = rankx(ALLSELECTED(Sales[product]);CALCULATE(SUM(Sales[sales]));sum(Sales[sales]))
when you write it out, as rankx adds an implicit calculate. Without this, a viewer could think that
rank = rankx(ALLSELECTED(Sales[product]);SUM(Sales[sales]))
would also be correct
:)
Thank you for this comment! Between this and the video, I was able to achieve want I needed to.
Hi Dennis, what of two values are same and share the ranks ? How to create Pareto in such a case ?
Excellent job! I normally don't watch videos like this, but I'm very glad that I did. You can clear, helpful explanations. The step by step walk-through was very useful for a thorough learning experience.
I've checked other videos, however your video is the one that helped me succeed with Pareto's in Power BI. Iwould also be aware of ALL/ALLSELECTED as highlighted by Dennis Priester.Thank you both
Thank you so much , you are amazing ...Excellent work !
great job! Thank you so much. You made if very simple to understand.
Very well explained with logic
Very detailed explanation sir, Tq sir
Excellent video!!!... clear instruction of all logic and corresponding DAX syntax ... thank you so much Pradip
Excellent Job all around. You explained everything extremely well and provided multiple ways to understand it. Cheers.
Awesome! Thanks a lot. That helped me to create my desired output within 12 mins.
@[Pradip Pardeshi] You Da Man! My DAX Guru! Keep up the good work, bro.
You made it really easy thank you so much sir
Great video! You explained this very well and easy to follow. I appreciate you walking through the functions and explaining how they worked.
As I am tweaking this to meet my needs I seem to be having an issue with the "Cumulative Total" formula when there is more than one description column. Example: Column A = Part number; Column B = Product Name; C = Total Sales, D = Rank, etc.... It is not adding up the totals, only duplicating the same number as the "Total Sales" column. In DAX Studio the TOPN([Rank],ALL only brings back the first row. However, with only one "Description" value this works just fine. Thanks again.
Thank you Pradip, it worked for me. Thanks a lot
Awesome, now I can mark that task as completed! Thanks!!
Thank you! Very good explanations.
superb step by step narration. wow
Sir, You expalined the topic very clear cut, we want explaination of some more DAX functions plz
Good for New Handler as a Good Tutorial point of view. Carry on. Good Work.
you saved my life! thank you
very nice and informative
Thankyou! very useful help on making pareto chart on power bi
Thank you
very simple and easy to follow
This video was extremely informative! Great work!
Great job. I would like to see this report with survey data from a questionnaire
Great Job!!!
Awesome explanation and easy to follow, thanks!
Excellent Job, Thank U!
Great,super,excellent thank allot
very clear and easy to understand, thank you!
Great video, easier than I thought!
Many thanks Pradip! Helped a lot!
Have you ever tried "Inlagd sill"? :) Very cool approach to cumulative values.
Very usefull. Thanku I have subscribed your channel
Thank you
Thank you very much, good sir!
Awesome Tutorial!! i subscribed u at once. Keep doing these kind of tutorials please. Very informative and nevertheless a short video!!!
Thank and easy to follow the How.
wonderful explanation Pradip. will this table will slice and dice on basis of other columns like Year, Month from other Dims please.
Really great. thanks
Cool! thanks a lot! Question) Is it possible to calculate in the seperate table that in total we had that number of products with acumulative 80% with that sum - like a card view?
I also would to do the Pareto for two dimensions. - category/product, do you have a clip for them? Thanks again.
Thank you some much !!! very usefull
Excellent Video!!!!!
very useful! thank you very much!
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?
Awesome!
Hi
I have a question
What if in the total sales Column if to product name has same value the rank will also be same for both the product
What is the solution for it?
Hi Pradip, thanks for this helpful video.
I have 1 question:
If there is a case where 2 products have the same "Total Sales" value, the Rank assigned to both of the products is same. In such cases, cumulative sales don't increase for the 2nd product.
Do you think this issue can be handled?
Hi Shreyas, I am having the same issue. Did you ever find a workaround for this issue?
Hi Shreyas, any solution to this issue ?
thanks, but if I want to add filter of date, I would like to have the accumulated filtred by years, Pls I need help. Thanks
Hello, this is very useful, I've managed to apply it to my use case.
Unfortunately I do get some rows where values are the same, resulting in duplicate RANK values, which stuffs it up. Any idea as to how to get around it. I need it to be a measure, calculated dynamically. Thanks
I have this same Issue. Im ranking based on count of values. Some the the items have the same count. What do we do.
Did you find out the answer to this issue ?
My rank function returns “1” for all the products in the table. What might be the problem? Am I not doing something right ?
but how to cumulative the product which have same rank and same sales but different product?
Have you found an answer to that?
First of all thanks for the video.
There's one thing that doesn't seem to work, and that's when two values have the exact same sales amount.
The first value is not added to the cumulative, the second one is added twice.
For example A = 20, B = 10, C = 10, will result in a pareto that looks like 50%, 50%, 100% instead of 50%, 75%, 100% and also the rank is 1,2,2 insted of 1,2,3.
Any way around this?
Hi! This works like charm!! However while making the chart , my cumulative percentage is not reaching 100%, any idea why??
Great video - one question how do you handle when the ranking has 2 numbers that is ranked equally?
Hi Rune, did you find a solution to this issue ? Having the same.
Excellent tutorial! But I have a problem, I have products with exact sales amounts and the fórmula ranks them in the same position (1,2,2,3,4,5), how do I do to rank them in different position (1,2,3,4,5,6) I need this urgently
Hi Jaoquin, did you find out the issue to this problem ? Please help .
Very helpful thank you
excellent, marvollus,super
does we create different measures in same tables?
That was excellent thanks
how to make that data label /text horizontal instead of diagonally
What if there is same total sales for two products
Very helpful thank you
Tutorial was excellent but sir without practice dataset there is no use.. Plz share data set
Thank you
Bravo!
super! thanks!
How to view your other videos on Power BI. Is there any website to view them or BOLG? Can i have your contact numbers to join fro training?
Pls writo me at pradip@techandtraining.biz
GREAATTT!! THANK YOU SO MUCH!!!
I can't get past the RANKX function. It gives a bunch of rank 1 and then skips to 9.
Great….
sir can u help me to show only 80% products here, want to see only 80% product, please help sir
thank you!
Hi. This solution didn't work if in column total sales are duplicates. How to solve this gap?
I found solution, so I'll answer by myself: To avoid problems with duplicates in bar chart you have to introduce there some noise to differentiate duplicate values. For example by adding column with some insignificant random values: New Total Sales= [Total Sales]+(RAND()/10000).
Source: radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
@@flyhigh1491 Brilliant way to get around this. Thanks!! :)