PowerPivot - % of SKUs required to get to 80% of Revenue

[eliwaite]

I have been searching for a solution to my problem for the better part of a day, so I thought I would post here hoping someone could help me out. Let me first start with the question I am trying to answer: What percentage of SKUs does it take to reach 80% of revenue. Here is a simplified chart to use as an example (we can call the table "Data"):

[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Revenue[/TD]
[TD]% of Revenue[/TD]
[TD]Rank[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA
[/TD]
[TD]40[/TD]
[TD]4%[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]500[/TD]
[TD]50%[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]200[/TD]
[TD]20%[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CBA[/TD]
[TD]200[/TD]
[TD]20%[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DDD[/TD]
[TD]60[/TD]
[TD]6%[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So in this example, the quickest way to get to 80% would be ABC+CCC+CBA (50%+20%+20%). Thus it takes 3 of the 5 SKUs to get to 80% of the revenue, thus the answer is 60%.

I have used the RANKX function to add the Rank column, but this may be a red herring. I thought I could use it to return the rank when the running total hit 80% or higher. But as you can see duplicates mess that up (CCC and CBA are tied so they both show a rank of 2. I also cannot figure out a formula to return the right rank anyways.

Final note, the % of Revenue is a calculated field, % of Revenue:=DIVIDE([Net Revenue],[Total Net Revenue]).

I would appreciate any guidance on this question that you can provide.

Thank you,
Eli

 

[Matt Allington]

Interesting problem. Lucky I am not in the mood for doing work

Here is a solution that uses calc columns and also measures. There may be a better way but I haven't found it. Maybe someone else will

https://www.dropbox.com/s/lz42hfep7o05o72/rank problem.xlsx?dl=0

 

[eliwaite]

Hi Matt,

Thanks for the example workbook. I like the approach you take to this, however I am running into a circular dependency error. I have read Marco Russo article on this Understanding Circular Dependencies in Tabular and PowerPivot - SQLBI and I believe I understand the problem of using CALCULATE in more than one column. However, I am interested in learning how you got around this problem, as your spreadsheet works.

Thanks,
Eli

 

[Matt Allington]

Ahhhhhh. I too have read Marco's book BUT I attended a full week of training just 2 weeks ago with Marco and this was on the agenda. So when I was working on this I was really excited to hit the problem and then use the solution. It actually took me about 5 minutes to find the menu option again - but this time I knew it was there and I just had to find it. I am on my iPad so can't check, but in short there is a menu item that brings up a dialog and allows you to set the primary key of a table. This is done automatically if you have a join with unique values. I meant to mention it but obviously forgot.

I will take a look when I am at my PC and let you know.

 

[eliwaite]

I actually found it, if you activate the Advanced Controls you can then set the Key field. So that problem has been solved.

I would like to add an extra wrinkle to this. What happens if the SKUs are broken down by, say a region. The Running Share formula does not start a new for each region, it is one big running total. I have tried adding ALL(Date[SKU], ALLEXCEPT(Data,Data[Region]) as a filter, I have also tried wrapping the formula in another CALCULATE with these filters on the wrapper. I cannot seem to get it work. I have updated your spreadsheet with Regions: https://drive.google.com/file/d/0B57ZZe_petMKRFhIMW5pa2Ztcnc/view?usp=sharing

If you have additional insights into this problem I would appreciate it.

 

[Matt Allington]

Try this

https://www.dropbox.com/s/qstiql0xkt9nku4/rank problem region.xlsx?dl=0

 

[eliwaite]

The extra EARLIERs work... however it is very static. I would like to create the pivot table so it is dynamic. So you can drop the Regions in and see how regions shape up, and then remove the Regions and see how things look on a worldwide basis. This is why I was leaning towards a ALLEXCEPT(Data,Data[Region]), but I cannot seem to get that to work right.

 

[eliwaite]

As I have been playing with the file you shared with me, I realized the Share was not calculating correctly when switching from a Region view to a WW view. I put the formula in the Calculated Field area and changed it to:
Share 2:=DIVIDE(CALCULATE(sum(data[Revenue])),CALCULATE(sum(data[Revenue]),ALLEXCEPT(data,data[Region])))
As the Running Share uses Earlier, I had to leave it int he Calculated Column area, but changed it to:
=CALCULATE(SUMX(data,[Share 2]),FILTER(ALLEXCEPT(data,data[Region]),data[Revenue]>=EARLIER(data[Revenue])))
This formula results in the Region running total showing regardless if the Region is included in the pivot table or not.
The updated file can be found here https://drive.google.com/file/d/0B57ZZe_petMKd0xKU1JvNkhlVDg/view?usp=sharing

If you can figure out how to write a formula which will allow the filters in the pivot table to change the results appropriately, that would be amazing.

Thanks,
Eli

 

[eliwaite]

Use this link for the updated file https://drive.google.com/file/d/0B57ZZe_petMKUjlDR3c5VVpQSEk/view?usp=sharing

I add a Unique Key field so the SKU are not the unique key. I also added SKU AAA to both AMS and EMEA (as the same product could be sold in more than one region).

 

[ImkeF]

Have a look at the dynamic ABC Classification pattern. Simply create A with 80% and filter on it (deselect B&C).