# ABC analysis

#### joe675

##### Board Regular
Hello, I am trying to create an ABC analysis for some data.

I am using the book "Microsoft PowerPivot for Excel 2010 Give Your Data Meaning" which gives the steps, their data is pretty simple.

My data is a little more complicated.

I have the sales data broken down by "Market", "Company","Category", "UPC" and "Period Description".

What I am trying to do is create an ABC analysis of sales of the UPCs of each Company for each "Period Description".

Where I am having an issue is when I create the cumulative total for the "UPCs" for "Company" and "Period Description", I get the cumulative for all companies and all Period Description. I just need the cumulative total for UPC, Company and Period Description

I have uploaded a copy to Skydrive.

Thank you

https://skydrive.live.com/redir?resid=84AA25B86224666C!142&authkey=!AG6UZx_FiOwprKU

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I uploaded an image which show the formula I am using to create the cumulative amount.

The amount in the cell I point to should be \$80,701.

Hope this helps show what I need

Thank you

ImageShack® - Online Photo and Video Hosting

Joe, thanks for uploading the workbook, it's a massive help when it comes to solving the problem!

Marco and Alberto's book is undoubtedly great but it could potentially be argued that this particular technique is a little outdated given it relies so heavily on calculated columns. The downside is that they calculate on refresh and are not dynamic based on filters, selections etc. There are ways of tackling this problem using measures that are way more flexible although it requires a deeper understanding of DAX.

That said the major bonus in this instance is that by applying the ABC band to the calc column you can can then easily use it as a column header or to populate a report filter/slicer.

The issue here was basically that your cummulative column accumulates of the whole table rather than just that company for that time period (which I think is what you are trying to do, the image you shared doesn't work so it isn't totally clear). I adjusted your cummulative measure to just work on that company in that time period:

Code:
``````=CALCULATE(
SUM(Data[SalesAmountProduct]),
FILTER(
ALL(Data),
Data[SalesAmountPRODUCT]>=EARLIER(Data[SalesAmountPRODUCT])
),
Data[Company]=EARLIER(Data[Company]),
Data[Period Desc]=EARLIER(Data[Period Desc])
)``````

I then wrote a near identical measure to enable me to get the cummulative % on that subset.

I uploaded my workbook on SkyDrive: http://sdrv.ms/Z0XufG

You could also have a look at some measure based techniques for doing similar things, this one by Gerhard is particularly creative: Dynamic ABC Analysis in PowerPivot using DAX | Gerhard Brueckl's BI Blog

Jacob

Perfect, thank you so much!

I was racking my brain for hours trying to figure it out.

Replies
2
Views
213
Replies
0
Views
379
Replies
0
Views
520
Replies
0
Views
429
Replies
0
Views
3K

1,196,073
Messages
6,013,267
Members
441,758
Latest member
Abren

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back