Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

20 / 80 Calculation

Hi,

I have the need to calculate the following "The percentage of sales that my top 20% (number of) suppliers are responsible for".

A classical 20/80 check which is fairly simple to do in Excel.

My challenge here is that I need this number over time as well, so basically a table like:

Year Month      | 20% Supplier Sales  | 100% Sales | Top 20% Suppler Share

201504            | 632                          |  1000          | 63,2%

201504            | 600                          | 1200           | 50%

I am not interested in which the top 20% suppliers are, so no need for the "Pareto"-selection button.

Attached is a dummy application, where I have excluded the complexity of "time", but where the end result would be something like:

Year Month      | 20% Supplier Sales  | 100% Sales | Top 20% Suppler Share

XXXXX           | 750                          |  1520       | 49,34%

ANY ideas are appreciated.

Kind regards

Niklas

PS Please, notice that I could achieve the result by using all needed dimensions, but I need to narrow it down to one fantastic aggr/sum/if/setanalyis/percentile-formula, which could be used without showing all suppliers/names etc.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try:

Variable vTopX: =ceil(count(distinct Name)*0.20)

Expression: =num(sum({<Name={"=rank(sum(Amount))<=$(vTopX)"}>}Amount)/sum(Amount),'#.00%')


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

Try:

Variable vTopX: =ceil(count(distinct Name)*0.20)

Expression: =num(sum({<Name={"=rank(sum(Amount))<=$(vTopX)"}>}Amount)/sum(Amount),'#.00%')


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Ouch. You are so good. Just excellent!

(I do hope that you had that solution in your back pocket and did not figure it out from scratch in 3 minutes. Took me probably 5 minutes to write down the question...)

Kind regards

Niklas

Anonymous
Not applicable
Author

Can I make it more challenging and add the time dimension to the problem...

See updated attached file with.

Kind regards

Niklas

Gysbert_Wassenaar

See attached qvw. Is that what you need?


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Unfortunately not. That is cheating using the Name-dimension. 😉

I can only have Time and Expression.

(We are actually talking about a couple of thousand "Names")

The "Over Time"-table is basically what I need, but with correct calculation(s) (disregard the "Top Names", which is just a test).

From what I have read I am a bit hesitant that set analysis will do the job over several rows, given that that will be an initial selection for the chart. Might have to revert to precalculations in the script.

(Please, do not overspend your valuable time on this problem unless you also find it interesting/challenging.)

Kind regards

Niklas

robert99
Specialist III
Specialist III

Thanks. Just what i need