ABC analysis

[joe675]

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.

Could someone please help me out?

Thank you

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

 

[joe675]

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

 

[Jacob Barnett]

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:

=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

 

[joe675]

Perfect, thank you so much!

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