Forum Discussion

scabral's avatar
scabral
Icon for Helper IV rankHelper IV
3 years ago
Solved

RANK within group when filtering

Hi,   I have the following data table in Power BI with a sequence by acct with highes value to lowest value:   Acct LocationKey Value Type Seq 1 AAB 300 PH 1 1 AAC 550 HE 2 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi scabral 
    Please refer to attached file with the solution

    Rank = 
IF (
    HASONEVALUE ( 'Table'[LocationKey] ),
    RANKX ( 
        CALCULATETABLE ( 
            VALUES ( 'Table'[LocationKey] ), 
            ALLEXCEPT ( 'Table', 'Table'[Acct], 'Table'[Type] ) 
        ),
        CALCULATE ( 
            SUM ( 'Table'[Value] ), 
            ALLEXCEPT ( 'Table', 'Table'[LocationKey] ) 
        ),,
        ASC,
        Dense
    )
)

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi scabral 
Please refer to attached file with the solution

Rank = 
IF (
    HASONEVALUE ( 'Table'[LocationKey] ),
    RANKX ( 
        CALCULATETABLE ( 
            VALUES ( 'Table'[LocationKey] ), 
            ALLEXCEPT ( 'Table', 'Table'[Acct], 'Table'[Type] ) 
        ),
        CALCULATE ( 
            SUM ( 'Table'[Value] ), 
            ALLEXCEPT ( 'Table', 'Table'[LocationKey] ) 
        ),,
        ASC,
        Dense
    )
)