Creating cumulative measure based on dynamic ranking / Dynamic running total/pareto diagram-measure

[Kalisto87]

Hi,

I have a very interesting problem I believe:

I need help with creating dynamic totals/pareto diagram on suppliers based on sales, and categorize the supplier in categories based on their acculmulated sales. I.e a A supplier is one of the supplier in the interval of 0-80% accumulated sales (ABC-analysis). 

My data is a large table with all the spend of a company. This means multiple rows per supplier containing all single purhaces from all suppliers, including what year the purchase was made. 

I have done quite som googling and know about the solution calculate(sum(sales),allselect(date,date<=max date))). However this does not work on my table as I want the ranking/cumulative sales/category/ to change in the report with.

This was the result when using the above formula, but the result does not care about what year is selected. (The peak is just a double entry error in the data set)

Now, the main issue as I understand it: 

I have made a dynamic ranking measure with the formula Ranking1 = RANKX(ALLSELECTED('Sales'[SupplierID]);CALCULATE(SUM('Sales'[Sales]))), and it works great. I also have dynamic sum measures, and percentage of total spend per supplier, all dynamic with year selected.

The problem is when I try to accumulate the percentages to create a pareto diagram as I did above (I followed this receipe to create the "static" pareto https://powerbi.tips/2016/10/pareto-charting/). Is is not possible to create a cumulated measure based on a ranking measure as I understand.  This is what I want to work: 

**bleep**. total = CALCULATE(SUM('Sales[Sales]);FILTER(ALLSELECTED('Sales');[Ranking1] <= MAX([Ranking1]))). However this gives the error on the max function, because it cannot calculate max on the measure.

This is the dynamic results I have so far, but I have no way of cumulating the percetages og categorixing suppliers based on them..

Any help would be greatly appreciated!

[Vvelarde · Accepted solution]

@Kalisto87

Hi, try reviewing this blog's post.

Is in spanish but i hope can help you.

https://www.pbiusergroup.com/blogs/victor-velarde/2017/07/07/como-realizar-un-diagrama-8020-o-de-dis...

Regards

Victor

Lima - Peru