Top 2 by group in DAX Power BI

1
votes

I am trying to get the Top 2 units by company here. Table is called 'Table (3)'

enter image description here

I want to be able to populate the column like this -

enter image description here

I had tried Column = RANKX(ALLEXCEPT('Table (3)','Table (3)'[Company]),SUM('Table (3)'[Units])) but got a circular error.

The other way I think of doing this - not very effective - is use the TOPN and do a UNION by each company so each company would have a table of it's own TOPN value.

I know how to do this on power query already using Table.MaxN but want to do this on DAX

1
powerbidaxpowerquery

1 Answers

1
votes

You can get the rank like this:

Rank = 
VAR CurrUnits = 'Table (3)'[Units]
RETURN
    CALCULATE (
        RANK.EQ ( CurrUnits, 'Table (3)'[Units] ),
        ALLEXCEPT ( 'Table (3)', 'Table (3)'[Company] )
    )

From there, you can throw away ranks other than 1 and 2 if you choose to.