Powerpivot DAX Rank products by quarterly sales with monthly data

Question

I'm trying to create a calculated column that assigns a rank to each Product based on its sales in each combination of Country, Store, and Quarter.

However, the data in the table is monthly.

I was thinking of using SUMMARIZE() to create the quarterly view and then use RANKX() on that, but the value for the sum of sales cannot be evaluated for each column if I use summarize.

In order to get the rank for the monthly view I have been using the following formula:

=
RANKX (
    FILTER (
         Table,
           Table[Country] = EARLIER ( Table[Country])
                && Table[StoreType] = EARLIER ( Table[StoreType])
    ),
    [Sales]
)

Simplified the data looks like this with the calculated column for monthly rank and the quarterly one I'm trying to figure out

Country   StoreType   Month   Product  Sales  MonthlyRank  QuarterlyRank
  USA         A       Jan-15     P1      10         1            2
  USA         A       Jan-15     P2      15         2            1
  USA         B       Jan-15     P1       5         1            2
  USA         A       Feb-15     P1       5         3            2
  USA         A       Feb-15     P2      20         1            1
  USA         A       Feb-15     P3      10         2            3
  USA         A       Mar-15     P1      10         2            2
  USA         A       Mar-15     P2      25         1            1
  USA         B       Mar-15     P3      15         1            1

How could I be able to achieve this?

greggyb greggyb · Accepted Answer · 2015-11-23T11:50:43

You need to add a column to your date dimension that uniquely identifies each quarter. Why don't we call this unique column something like QuarterKey or QuarterIndex.

Then you can do the following in your SUMMARIZE():

ADDCOLUMNS(
    SUMMARIZE(
        FactSales
        ,DimGeography[Country]
        ,DimStore[Store]
        ,DimDate[QuarterKey]
    )
    ,"SumSales"
    ,CALCULATE( SUM( FactSales[Net Amount] ) )
)

Powerpivot DAX Rank products by quarterly sales with monthly data

1 Answers