Forum Discussion

Helper I

6 months ago

Solved

Advanced Ranking (Rankx)

Hi,

I am struggling with ranking measures in Power BI. I have visual sorted descening by Number of Points (field Current) and I want to rank all filtered Analysts by this measure. I cannot share my workbook there as it is strictly confidential but let's imagine below data:

Perfect Rank	Rank (Current)	Analyst	Corp Rank	Team	Current	Raw	Weighted
1	1	Ann	a	Sales	4666	445	4666
2	2	Tom	a	Finance	4000	555	4000
3	3	Henrik	a	HR	3600	56	3600
4	4	John	a	HR	3580	456	3580
5	1	Johanna	b	Sales	3000	666	3000
6	2	Robert	b	Sales	2900	666	2900
7	3	Sarah	b	Sales	2899	567	2899
8	4	Joy	b	Sales	2801	7888	2801
9	5	Patrick	b	Sales	2800	788	2800
10	6	Steven	b	Sales	2780	788	2780
11	1	Alicia	c	Finance	2705	7858	2705
12	2	Carol	c	Finance	2700	7888	2700
13	3	Ursula	c	Finance	2690	7888	2690
14	4	Adrian	c	Finance	2600	888	2600
15	5	Milos	c	Finance	2560	88885	2560
16	1	Dorothy	d	Account	2501	4	2501
17	2	Tanja	d	HR	2500	447	2500
18	3	Chris	d	HR	2407	788	2407
19	4	Dominika	d	HR	2406	448	2406
20	5	Nicola	d	HR	1000	4888	1000

CurrentPoints is a measure dependednt on user selection in slicer: Raw or Weighted [Points]

Final formula look

CALCULATE (

[Points],

KEEPFILTERS ( 'BV'[Last or prev] = "Last" ),

// Limit to clients that pass the slicer gating

KEEPFILTERS (

FILTER (

ALLSELECTED ('BV periods'[Client]),

[Client Filter (by Slicer)] = 1

)

)
[Points] is formula which switches to raw or weighed, based on user selection. Last or Prev is filter for current period

I tried with simple formula:

Rank =

RANKX(ALL('BV'[Analyst]),[Current],,DESC,Dense) but it produces only 1 for all Analysts

Copilot adviced me another formula to exclude other fields used in visual:

Rank (Current) =

VAR _thisScore =

CALCULATE(

[Current],

REMOVEFILTERS('BV'[Team]),

REMOVEFILTERS('BV'[Corp Rank])

)

-- Distinct analysts in the currently selected slicer context,

-- but explicitly ignoring Team and Corp Rank row context

VAR _analystSet =

CALCULATETABLE(

VALUES('BV'[Analyst]),

ALLSELECTED('BV'),

REMOVEFILTERS('BV'[Team]),

REMOVEFILTERS('BV'[Corp Rank])

)

-- Compute Current for each analyst in that set, ignoring Team/Corp Rank

VAR _set =

ADDCOLUMNS(

_analystSet,

"Score",

CALCULATE(

[Current],

REMOVEFILTERS('BV'[Team]),

REMOVEFILTERS('BV'[Corp Rank])

)

RETURN

IF(

ISBLANK(_thisScore),

BLANK(),

RANKX(

_set,

[Score],

_thisScore,

DESC,

DENSE

)

And it produced almost corect ranks but recalculated after every Corp Rank. Do you have any ideas what I can do?

I want this rank to be sensitive to filters I have in report.

This worked with the sample I set up based on what you provided.

Selected Analyst Rank = 
VAR _analystCurrent = 
    CALCULATETABLE( 
        SUMMARIZECOLUMNS( 'Sample'[Analyst], "currentSum", [Current] ), 
        ALLSELECTED( 'Sample' )
    )
VAR _analystCurrentRank = RANK( _analystCurrent, ORDERBY( [currentSum], DESC ) )
RETURN
_analystCurrentRank

For visibility, sample semantic model and data I tested. Given that [Current] is a measure, I used the following sample data to more fully test the above:

Current = SUM( SampleData[Current Vals] )

Sample (as shown)

Perfect Rank	Rank (Current)	Analyst	Corp Rank	Team	Raw	Weighted
1	1	Ann	a	Sales	445	4666
2	2	Tom	a	Finance	555	4000
3	3	Henrik	a	HR	56	3600
4	4	John	a	HR	456	3580
5	1	Johanna	b	Sales	666	3000
6	2	Robert	b	Sales	666	2900
7	3	Sarah	b	Sales	567	2899
8	4	Joy	b	Sales	7888	2801
9	5	Patrick	b	Sales	788	2800
10	6	Steven	b	Sales	788	2780
11	1	Alicia	c	Finance	7858	2705
12	2	Carol	c	Finance	7888	2700
13	3	Ursula	c	Finance	7888	2690
14	4	Adrian	c	Finance	888	2600
15	5	Milos	c	Finance	88885	2560
16	1	Dorothy	d	Account	4	2501
17	2	Tanja	d	HR	447	2500
18	3	Chris	d	HR	788	2407
19	4	Dominika	d	HR	448	2406
20	5	Nicola	d	HR	4888	1000

SampleData

Perfect Rank	Current Vals
1	4346.291343
1	319.7086567
2	2465.796833
2	1534.203167
3	1583.478001
3	2016.521999
4	644.1608684
4	2935.839132
5	2442.290728
5	557.7092718
6	1545.220047
6	1354.779953
7	1125.799336
7	1773.200664
8	1602.011446
8	1198.988554
9	297.5253945
9	2502.474606
10	37.11925691
10	2742.880743
11	650.5460141
11	2054.453986
12	1057.462128
12	1642.537872
13	769.252108
13	1920.747892
14	377.7934778
14	2222.206522
15	2340.171915
15	219.8280848
16	456.5210337
16	2044.478966
17	1425.995879
17	1074.004121
18	17.09018432
18	2389.909816
19	728.0278296
19	1677.97217
20	710.2578621
20	289.7421379

5 Replies

MarkLaf

Super User

6 months ago

This worked with the sample I set up based on what you provided.

Selected Analyst Rank = 
VAR _analystCurrent = 
    CALCULATETABLE( 
        SUMMARIZECOLUMNS( 'Sample'[Analyst], "currentSum", [Current] ), 
        ALLSELECTED( 'Sample' )
    )
VAR _analystCurrentRank = RANK( _analystCurrent, ORDERBY( [currentSum], DESC ) )
RETURN
_analystCurrentRank

For visibility, sample semantic model and data I tested. Given that [Current] is a measure, I used the following sample data to more fully test the above:

Current = SUM( SampleData[Current Vals] )

Sample (as shown)

Perfect Rank	Rank (Current)	Analyst	Corp Rank	Team	Raw	Weighted
1	1	Ann	a	Sales	445	4666
2	2	Tom	a	Finance	555	4000
3	3	Henrik	a	HR	56	3600
4	4	John	a	HR	456	3580
5	1	Johanna	b	Sales	666	3000
6	2	Robert	b	Sales	666	2900
7	3	Sarah	b	Sales	567	2899
8	4	Joy	b	Sales	7888	2801
9	5	Patrick	b	Sales	788	2800
10	6	Steven	b	Sales	788	2780
11	1	Alicia	c	Finance	7858	2705
12	2	Carol	c	Finance	7888	2700
13	3	Ursula	c	Finance	7888	2690
14	4	Adrian	c	Finance	888	2600
15	5	Milos	c	Finance	88885	2560
16	1	Dorothy	d	Account	4	2501
17	2	Tanja	d	HR	447	2500
18	3	Chris	d	HR	788	2407
19	4	Dominika	d	HR	448	2406
20	5	Nicola	d	HR	4888	1000

SampleData

Perfect Rank	Current Vals
1	4346.291343
1	319.7086567
2	2465.796833
2	1534.203167
3	1583.478001
3	2016.521999
4	644.1608684
4	2935.839132
5	2442.290728
5	557.7092718
6	1545.220047
6	1354.779953
7	1125.799336
7	1773.200664
8	1602.011446
8	1198.988554
9	297.5253945
9	2502.474606
10	37.11925691
10	2742.880743
11	650.5460141
11	2054.453986
12	1057.462128
12	1642.537872
13	769.252108
13	1920.747892
14	377.7934778
14	2222.206522
15	2340.171915
15	219.8280848
16	456.5210337
16	2044.478966
17	1425.995879
17	1074.004121
18	17.09018432
18	2389.909816
19	728.0278296
19	1677.97217
20	710.2578621
20	289.7421379

I_miss_tableau
Helper I
6 months ago
Thank you. It works!

Zanqueta

Super User

6 months ago

Hi I_miss_tableau , please try it

Rank (Current) :=
VAR ScoresByAnalyst =
    ADDCOLUMNS (
        ALLSELECTED ( 'BV'[Analyst] ),    -- or 'Dim Analyst'[Analyst]
        "Score", [Current]
    )

VAR ThisAnalyst =
    SELECTEDVALUE ( 'BV'[Analyst] )       -- or 'Dim Analyst'[Analyst]

VAR ThisScore =
    CALCULATE (
        [Current],
        KEEPFILTERS ( 'BV'[Analyst] = ThisAnalyst )
    )

RETURN
IF (
    ISBLANK ( ThisScore ),
    BLANK (),
    RANKX (
        ScoresByAnalyst,
        [Score],
        ThisScore,
        DESC,
        DENSE
    )
)

pcoley
Super User
6 months ago
I_miss_tableau please try using visual calculations.
Select your matrix or table visualization at the report, RightClick and select New VisualCalculations:
Add a Custom Column with this calc:
RankCalc = RANK(DENSE; ORDERBY([Weighted]; DESC))

I hope this helps, if so please accept it as a solution, kudos are welcome
- pcoley
  Super User
  6 months ago
  I_miss_tableau Another option is to recreate the table presented at the report.
  
  in the Dax Query View select the script associated with summarizecolumns that recreates the table used by powerbi to create your visualization.
  use this query as the table reference in your rank measure

Forum Discussion

Advanced Ranking (Rankx)

5 Replies

Related content

Rank vs Rankx

RANK and RANKX DAX function

Why rankx function shows duplicated rank?

Duplicate ranking

RANKX Formula Returning Duplicate Ranks