0

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?

ConstantinHH
  • 1
  • 2

1 Answers1

0

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] ) )
)
greggyb
  • 3,728
  • 1
  • 11
  • 32
  • I still get the error that there is no current value for the current row when I put this into the rankx function. – ConstantinHH Nov 23 '15 at 17:09
  • Which makes sense, as the summarised table would have only 1/3 the rows – ConstantinHH Nov 23 '15 at 17:10
  • Perhaps throw us some sample data and the DAX you've tried so far? Your expected output will be very helpful as well. I've re-read your original question and realize that I misinterpreted your question. I thought you were asking how to group by those three fields. I actually haven't a clue what you're trying to do, or what your end result looks like. – greggyb Nov 23 '15 at 19:23
  • Just added sample data and code to the original question – ConstantinHH Nov 26 '15 at 18:05