RANK within group when filtering

[scabral]

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
1	AAD	700	PH	3
1	AAE	788	HE	4
1	AAF	980	PH	5
2	BBA	450	HE	1
2	BBB	480	PH	2
2	BBC	700	PH	3

If i place the data in a table in Power BI and filter by Type = "PH", i get this:

Acct	LocationKey	Value	Type	Seq
1	AAB	300	PH	1
1	AAD	700	PH	3
1	AAF	980	PH	5
2	BBB	480	PH	2
2	BBC	700	PH	3

What I want is a new measure that will re-rank the sequence based on the filter like this:

Acct	LocationKey	Value	Type	Seq
1	AAB	300	PH	1
1	AAD	700	PH	2
1	AAF	980	PH	3
2	BBB	480	PH	1
2	BBC	700	PH	2

thanks

Scott

[CNENFRNL]

[ppm1]

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

[tamerj1 · Accepted solution]

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