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
    )
)

     

ppm1's avatar
ppm1
Icon for Solution Sage rankSolution Sage
3 years ago

Please also try this measure expression, that should also work when there is no external filter on Type (i.e., match your original sequence #s).

Acct Rank =
RANKX (
    CALCULATETABLE (
        SUMMARIZE ( T5, T5[Type], T5[LocationKey] ),
        ALLSELECTED ( t5 ),
        VALUES ( T5[Acct] )
    ),
    CALCULATE ( SUM ( T5[Value] ) ),
    ,
    ASC
)

Pat