Forum Discussion

Kmoore15's avatar
Kmoore15
New Member
8 years ago
Solved

DAX Ranking within a group

Hello,   I am new to PowerPivot and am struggling to accompish what I am trying to do. I have a data set and I am trying to identify the total freight cost for all billing documents after the first...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI Kmoore15

     

    You could try adding this DAX column to your model

     

    Rank = CALCULATE(
                COUNTROWS('Table1'),
                FILTER(
                    ALL(Table1),
                    'Table1'[Sales document] = EARLIER('Table1'[Sales document]) && 
                    'Table1'[Freight Cost] > EARLIER('Table1'[Freight Cost])
                    )
                    )+1
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

HI Kmoore15

 

You could try adding this DAX column to your model

 

Rank = CALCULATE(
                COUNTROWS('Table1'),
                FILTER(
                    ALL(Table1),
                    'Table1'[Sales document] = EARLIER('Table1'[Sales document]) && 
                    'Table1'[Freight Cost] > EARLIER('Table1'[Freight Cost])
                    )
                    )+1
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Phil_Seamark your formula works but it seems to really hurt performance when you have a large amount of data with multiple different groups. I think there may be a more efficient way using RANKX. The team at SQLBI has a more in-depth explanation here for those interested. Essentially RANKX is optimized for performing these types of calculations

 

Rank =
RANKX (
    FILTER (
        'Table1',
        Table1[Sales  document] = EARLIER ( Table1[Sales  document] )
    ),
    Table1[Billing document],
    , // leave value argument blank
    ASC
)

 

I have a table with 250,000+ rows and 105 different groups (tracking price history for multiple different equities). The COUNTROWS method takes 30+ seconds and sometimes doesn't even have enough memory to compute, whereas RANKX takes 1-2 seconds.