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
Kalisto87
Frequent Visitor

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)

 

Capture.PNGCapture2.PNG

 

 

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!

 

Capture3.PNG

 

2 ACCEPTED SOLUTIONS

@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




Lima - Peru

View solution in original post

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

19 REPLIES 19
Kalisto87
Frequent Visitor

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

 

bug with report level filter.PNG

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.

v-jiascu-msft
Employee
Employee

Hi @Kalisto87,

 

The Ranking 1 is a measure, which means it's dynamical. I think some functions, such as summarizeaddcolumn, could help in this scenario that another measure bases on a dynamical measure. But we can't do more without data. Please share a dummy sample .pbix file.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Update.PNG

 

Any help is greatly appreciated!

 

/André

Anonymous
Not applicable

Hi,

 

Could you please let me know how did you calculate %GT accumulative total spend.

 

I have a similar issue, incase if you found the solution please do let me.

 

Regards,

Nagesh

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

I have a similar use case, can you please share the pbix in which the solution is implemented?

Thanks in advance

I do not have the file.  Describe the question, share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

Thanks for the reply and solution.

 

A little tweak of the formula seems to solve the problem, I guess it has something to do with the && logic in countrows.

 

Changing the measure for B to this solved the issue for me:

B - 80-95% of spend = COUNTROWS(FILTER(VALUES(Sheet1[SupplierName]);[Accumulated spend per supplier]/[Total spend]<=0,95))-[A - 80% of spend]

 

However, why is it not possible to select and slice the data by choosing for example the A category and get the other visuals updated?

 

Thanks

 

/André

Hi,

 

The A,B and C categories are the result of a measure.  Had they been values lying in a column, then you would have been able to slice and dice by clicking.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 Hi @Ashish_Mathur,

 

Thanks for the reply!

 

Is there no way of making the slice and dice option available? 

 

Hi,

 

I don't think so.  Not as long as A,B and C are measures.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks alot for the replies!

 

I have uploaded a sample Pbix.  

 

I hope dropbox works, if not I can upload somehere else

 

https://www.dropbox.com/s/gviw1r3gmdm3ssx/Dummy1.pbix?dl=0

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

@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




Lima - Peru


@Vvelarde wrote:

@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


 

Thanks for the reply. I tried the solution but I could not get it to work on my data. I ended up with a never ending list of syntax errors that I was unable to correct even though I tried to follow the formula.  (had to exchange the part CALCULATE ( VALUES ( Ventas[Nombre] ) ) with just Sheet1(SupplierID). 

 

Perhaps someone else has better luck?

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.