Pareto Calculation showing wrong results after slicing with year column

[Akhil_1411]

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

[lbendlin · Accepted solution]

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]

@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.⚡

[Akhil_1411]

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.

[parry2k]

@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.⚡

[parry2k]

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

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.⚡

[Akhil_1411]

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 

[aj1973]

Hi @Akhil_1411 

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

[Akhil_1411]

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