Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Akhil_1411
Helper IV
Helper IV

Pareto Calculation showing wrong results after slicing with year column

Hi,

 

I am creating a pareto chart with the below logic, but when i slice down with year it is giving me wrong percentage. (showing overall percentage is 22%)

can anyone help me. Also, attached the image PBIX file for your reference. ( Download wetransfer link: https://we.tl/t-vbYAWDqqLK )

 

Logic:- 

Pareto Measure =
Var Totalsales = CALCULATE(SUM(Orders[Sales]),ALL(Orders))
var currentsales = SUM(Orders[Sales])
var summarizetable=
SUMMARIZE(
ALLSELECTED(Orders),
Orders[Sub-Category],
"Sales",SUM(Orders[Sales])
)
var cumulative =
SUMX(
FILTER(summarizetable,[Sales]>=currentsales),
[Sales]
)
return
cumulative/Totalsales
 

pareto image.PNG

 

 

 
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

This approach using RANKX and TOPN instead seems to be more straightforward.

https://www.dutchdatadude.com/power-bi-pro-tip-pareto-analysis-with-dax/

 

View solution in original post

8 REPLIES 8
lbendlin
Super User
Super User

This approach using RANKX and TOPN instead seems to be more straightforward.

https://www.dutchdatadude.com/power-bi-pro-tip-pareto-analysis-with-dax/

 

parry2k
Super User
Super User

@Akhil_1411 couple of things, you have to change ALL to ALLSELECTED  in TOTASALES var

 

2nd, you have a filter on bar chat for the year 2016, not sure why but I guess you were testing something. Remove this filter. Try these and let me know how it goes?

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k ,

 

I Changed to All to Allselected. But, when i apply a top N filter like top 10 subcategories it is filtering to 100 %. It should show the corresponding percentage of top 10 category values.
Note: i want this filter as a end user can select the year and see the results.

 

File: - https://we.tl/t-QYgrZbaYbe 

 

Thanks,

Akhil.

@Akhil_1411 yes, it should work, once you update the measure and then you can use the year as a slicer, it already worked for me and I test it on your file at my end.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@Akhil_1411 here it is after the measure change, works perfectly fine

 

2020-06-07_10-40-10.gif

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k ,

 

I Changed to All to Allselected. But, when i apply a top N filter like top 10 subcategories it is filtering to 100 %. It should show the corresponding percentage of top 10 category values.
Note: i want this filter as a end user can select the year and see the results.

 

File: - https://we.tl/t-QYgrZbaYbe 

Hi @Akhil_1411 

 

I opened a new page and Re created your visual. it is working fine using Year slicer. Some buging your visual.

Year Filtering.PNG

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hi @aj1973 ,

 

Pareto Rule : 80 -20 percentage. It should show 100%. If you see in your re-created chart the overall cumulative percentage is showing upto 26.05%. This is wrong

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.