locked
Pareto as calculated measure RRS feed

  • Question

  • I created some calculated column to make ABC analysis across products. I have all transactions in the main (fact) table, so the ABC calculation has to be made on an aggregation (by product) and it works (I'd like to write a blog post about this).
    However, I'm wondering if I could create a similar technique as calculated measure, working only inside the data filtered by users which may filter one region, one customer and so on.
    My formulas makes use of EARLIER function in calculated column, but I cannot use it in calculated measures (right?).
    Any suggestion will be appreciated (even an official "no way" answer...).

    Marco Russo
    Friday, December 25, 2009 8:21 PM

Answers

All replies

  • Hi Marco, can you provide some more explanation as to what you mean by ABC analysis?

    thanks,
    rob
    Sunday, December 27, 2009 5:48 PM
  • Definition here: http://en.wikipedia.org/wiki/ABC_analysis
    Let's say that:
    - Products that make 70% of the sales are in Class A
    - Products that make 20% of the sales are in Class B
    - Products that make 10% of the sales are in Class C

    To make the calculation, we need to calculate the sales for each product, sorting them by sales (from the best seller down to the worst one).

    The following is an excerpt of a writing I'm working on.

    Before starting, consider that we will use the ProductName and the SalesAmount columns in our formulas.

    ·         SalesAmountProduct – this is the sales amount for each Product; the same value is duplicated for each row of the same product. The EARLIER function get the product name of the current row and the FILTER returns all the rows for the sales of the same product.
    =SUMX(FILTER('Sales','Sales'[ProductName]=EARLIER('Sales'[ProductName])),'Sales'[SalesAmount])

    ·         CumulatedProduct – this value is the cumulated value of a product, considering them ordered from the top-seller down to the worst one. In this case, the EARLIER function is used to get the sales amount for the current product and the FILTER returns all the rows of the products that sold at least the amount value of the current product.
    =SUMX(FILTER('Sales','Sales'[SalesAmountProduct] >= EARLIER('Sales'[SalesAmountProduct])),'Sales'[SalesAmount])

    ·         SortedWeightProduct – This calculation simply transforms the CumulatedProduct calculation into a percentage. This number will be used to filter all the transactions according to the percentage limit corresponding to ABC classes (usually A = 70%, B = 20% and C = 10%).
    ='Sales'[CumulatedProduct]/SUM('Sales'[SalesAmount])

    ·         ABC Class Product – this is the final result of our calculation; depending of the value of SortedWeightProduct an A, B or C is displayed
    =IF('Sales'[SortedWeightProduct]<0.7,"A",IF('Sales'[SortedWeightProduct]<0.9,"B","C"))

     If you like, I can send you an example with data.


    Marco Russo
    Sunday, December 27, 2009 6:09 PM
  • Cool, thanks Marco.

    I think we'll need to wait for the DAX folks to return from holiday vacation.  I see a number of things in your current formulas that don't seem valid in measures, and am unsure what the workaround will be.  Marius generally seems able to come up with unexpected ways to get these things done :)

    -rob
    Monday, December 28, 2009 3:59 AM
  • Rob,

    reading a message from Marius in another thread I had with him, I think that using calculated column is the way to go for ABC analysis, just because I need to put the result (ABC class) as row/column/filter in the PivotTable, and not as a Value. These is an excerpt from Marius message:

    In my opinion, in practice the simplest rule to decide when to use measures vs. calculated columns is:

    1. If you want to place the (calculated) results in an Excel Slicer, or see them on Rows or Columns in a pivot table (as opposed to the Values/Data area), you must use a Calculated Column (in the PowerPivot table grid window).
    2. If you want the resulting calculation _values_ to reflect pivot table selections made by the user and see them in the Values area of pivot tables, you must use a Measure (in the PowerPivot Field List, in the pivot table).

    Marco Russo
    Monday, December 28, 2009 10:36 AM
  • I came to a similar conclusion some time ago. If you put a PowerPivot calculated column in a PivotTable values area, the calculations are always static (don't change with context), so they don't work there. On the other hand, if you need to categorize text or numbers in some way to use in a row/column/report/slicer, a calculated column is the way to go. So far, I've only used calculated columns in instances where I need a specific string (like firstname+lastname, month, year etc.), where I need to categorize data based on categories taken from a lookup table (the only way I could think of to simulate the text field grouping feature in Excel PivotTables) or where I need to segregate a numeric field into 'bins' (simulating the numeric grouping in Excel PivotTables or attribute discretization in a SSAS cube). In many ways, a PowerPivot calculated column is similar to a caluclated column in a SQL query...without having to learn SQL or (even if you know SQL) the specific function syntax of a DBMS.

    BTW, your ABC analysis calculations are great (!) and provide a technique that can be used in similar types of analyses. Your last calculation reminds me of a problem I have not been able to resolve satisfactorily in PowerPivot - that is, handling inexact lookups. Imagine, for example, you have a tax table in a worksheet and the table has 20 discrete tax rate 'brackets'. In Excel, VLOOKUP handles the rate lookup easily. However, in PowerPivot, the RELATED function (which is often considered as a superior VLOOKUP) can't handle this type of lookup...and using 19 nested IF functions in a formula isn't a practical solution.
    Monday, December 28, 2009 2:50 PM
  • <offtopic>
    Colin, please what is the country that has 20 discrete tax rate 'brackets'? I thought Italy was the most complicated tax system, but it seems we are not so bad!
    </offtopic>

    Talking about the issue of calculating tax table - yes, this is a scenario where you don't have an equivalent VLOOKUP function in DAX. In a broader view, this is the problem of having a very strict rule to join tables in a PowerPivot workbook (only one field, only equal values). I suppose that in V1 we will have to handle things in this way.

    Marco Russo
    Tuesday, December 29, 2009 9:08 AM
  • LOL! For income tax, I don't know. In Canada, the max is 8 or 9 depending on the province (Federal + Provincial taxes). I used "tax" in my posting because it's the first thing that came to mind...but you get the general idea.
    Wednesday, December 30, 2009 1:42 PM
  • hi Marco, has your questions been answered thro the thread with Marius
    Wednesday, January 20, 2010 3:06 PM
  • You might want to take a look at this post with a more complete discussion:

    http://sqlblog.com/blogs/marco_russo/archive/2010/01/19/abc-analysis-in-powerpivot.aspx

    Marco


    Marco Russo
    Monday, November 29, 2010 11:26 AM