ABC Classification - How To?

[edubois]

Hello and thank you in advance for your help!

I am using Excel 2013 Powerpivot with a three table data model.

I have tried various options/ideas but am enable to generate dynamic classifications A to C for my various locations at least by revenue and by year.

Here is a link to a test workbook for anyone suitable and willing to assist me:
https://filetea.me/t1sh9nF7UuBRfxOEnHY20nyDQ

Best regard

 

[scottsen]

Link to file isn't working for me. Just takes me to a blank page.

 

[edubois]

Link to file isn't working for me. Just takes me to a blank page.

How should I share the file then? What is the best practice on MrExcel?

Thank you

 

[edubois]

Link to file isn't working for me. Just takes me to a blank page.

Good morning,
Here is a link to the compressed file using Dropbox:
https://www.dropbox.com/s/sjoh0hbxnhjeu10/ABC Classification Test.zip

Thank you

 

[scottsen]

Sorry for the slow response, I am travelling this week. Dropbox worked great.

So, how do you define being an 'A'? In a given year, if I sorted by 'All Net Sale', I would include products in 'A' until i reached 80% of the net sales for the year?

 

[edubois]

Sorry for the slow response, I am travelling this week. Dropbox worked great.

So, how do you define being an 'A'? In a given year, if I sorted by 'All Net Sale', I would include products in 'A' until i reached 80% of the net sales for the year?

Your help is truly appreciated no matter what.

Class is annual by "NetSales" and by locations. It should go as follow:
Class A is total revenue of a location from $6,000,000 and over
Class B is total revenue of a location between $2,000,000 and $5,999,999
Class C is total revenue of a location between $1 and $1,999,999

We should end-up with a few class A locations, more of class B and many of class C.

A goal is to be able to classify all locations by ABC and year to then further analyse data within classes...

Be safe traveling!

 

[scottsen]

So... the main problem you have is that you are going to want to have your ABC on Slicers. That means it can't be dynamically calculated in a measure. It must be in a calculated column.

If we were happily ignoring Years, then we would just jam a calc on the References table:
TotalNet = SUMX(RelatedTable('ALL'), [All Net Sale])

Then do some work off TotalNet for the classification. IF (References[TotalNet] >= 6000000, "A", ...

I think you are going to have to have a LocationYear table where we can do this work.

Go read through this as well: ABC Classification – Dax Patterns

Make sense?

 

[edubois]

While I am interested in the percentage as described, it is not my first goal. Also, I am already currently trying the calculated column idea based on the resource linked above i found yesterday, but so far have no success as I want classification based on individual locations annual revenue and not based on total revenue of all locations. Again, the % isn't my focus for now.

I am fine with the slicers actually but can not ignore previous years. I'd want to have ABC and Years as slicers...

Thank you for the followup!

 

[scottsen]

I want classification based on individual locations annual revenue

Which is why I said "I think you are going to have to have a LocationYear table". You need some place to store your A, B or C... that is related to both a Location and Year. You need a table that has the granularity you care about.

I am fine with the slicers

Well, if you were fine NOT having slicers that would be a different conversation. I am assuming you NEED the ABC to be something you can filter on.... and therefore it needs to be done in a calculated column (as opposed to measure / calculated field). That is what makes your probably challenging really. Having an "A" show up in a pivot table CELL would be easier.

 

[marco.russo]

Hello and thank you in advance for your help!

I am using Excel 2013 Powerpivot with a three table data model.

I have tried various options/ideas but am enable to generate dynamic classifications A to C for my various locations at least by revenue and by year.

Here is a link to a test workbook for anyone suitable and willing to assist me:
https://filetea.me/t1sh9nF7UuBRfxOEnHY20nyDQ

Best regard

The pattern pubished now is a "static" ABC analysis. If you want a dynamic one, we are working on that but performance might be bad. How many rows you have in the table to classify?