Forum Discussion
Creating cumulative measure based on dynamic ranking / Dynamic running total/pareto diagram-measure
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!
Hi, try reviewing this blog's post.
Is in spanish but i hope can help you.
Regards
Victor
Lima - Peru
Hi,
You may refer to my solution in this workbook. There is one problem that when i select 2015 in the slicer, i get the result for "B" as 2 whereas it should be 3. I cannot figure out why that is happening. Hope someone else can pitch in.
19 Replies
Hi,
As in understand, you'd like to create a column where you want to accumulate the percentages in the prosentbridag column. Am i correct? Please share the link from where i can download your file.
Hi. You are correct.
The result I want is a measure/column that is dynamic and allows me to draw the pareto curve in a graph where it is possible to adjust year and get an updatet pareto for that year.
Based on the pareto calculation I also want to do a ABC-analysis to sort out the supplier. E.g. Suppliers that have cumulative sales < 80% is A-class suppliers.
I think I need a dynamic colum in a temp table to do this, but perhaps I am wrong.
Hi, try reviewing this blog's post.
Is in spanish but i hope can help you.
Regards
Victor
Lima - Peru
Thanks alot for the replies!
I have uploaded a sample Pbix.
I hope dropbox works, if not I can upload somehere else
Hi
v-jiascu-msft , Vvelarde, Ashish_Mathur
I have uploaded sample data as requested. I hope you can help!
/André
Hi Vvelarde
A quick update:
I was able to get the measure to work, and it works great! Thanks. However, I am not able to make a table counting distinct number of suppliers of each category of accumulated spend (so called ABC-analysis)
Please see the picture below to see the updated results. As marked in the red circle, the table does not understand that I want to see distinct number of suppliers in each category. Any ideas of how to remedy this problem?
The formula for the categorization is
Supplier Category = IF([Accumulated spend per supplier]/[Total spend]<=0,8;"A - 80% of spend";IF([Accumulated spend per supplier]/[Total spend]<=0,95;"B - 80-95% of spend";"C - 95-100% of spend"))
As you can see the formula works great when I list all suppliers in the table to the left, but not in the table to the right. I have also uploaded the updated .pbix model here: https://www.dropbox.com/s/gviw1r3gmdm3ssx/Dummy1.pbix?dl=0
Any help is greatly appreciated!
/André
I discovered that it is not possible to remove any of the suppliers in the report level filter, any ideas to why? The screenshot explains the error.
It is possible to selct only one supplier in the report level filter, but if I deselect one supplier the pareto formula crashes. Unfortunately the formula is too advanced for me to pinpoint where the error lies, and the Power BI error message does not help much..
Were you ever able to figure out the issue with multiple values provided but a single value expected? I am running into a similar issue where if the field I'm aggregating (customer name in may case, instead of supplier name) is filtered, the error is shown. It seems like it has to be all items in the table. Even creating just a portion of the formula in the dummy pbix file results in the error.
