Calculated column showing rank by a summary column

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
[TABLE="width: 500"]
<tbody>[TR]
[TD]Colour
[/TD]
[TD]Amount
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

How would I calculate the rank column given the first two columns as a calculated column? Basically each row looks at the colours and then sums the Amount for that colour. Then shows the rank for its colour. Ranks are therefore repeated in multiple rows.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Something like this should do it.
By the way, in your example shouldn't the ranks be Blue 1, Green 2, Red 3?

Code:
Rank =
RANKX ( 
    VALUES ( YourTable[Colour] ),
    CALCULATE ( SUM ( YourTable[Amount] ), ALLEXCEPT( YourTable, YourTable[Colour] ) )
)
 
Upvote 0
This worked perfectly of course.

Sorry to bother you, but would you mind explaining how it works? I understand both parameters of the RankX individually (ie Values creates list of unique colours and Calculate sums all the amounts for the current colour with other filters removed), but I'm not quite sure how RANKX puts that together in particular row.
 
Upvote 0
Sure,
The way I would describe it is:

  1. Behind the scenes, RANKX constructs a table consisting of the first table argument plus the second argument added as a calculated column.
    In your case that is a table looking like this:
    [TABLE="class: grid, width: 400"]
    <tbody>[TR]
    [TD]Colour[/TD]
    [TD]Expression Evaluated Per Row[/TD]
    [/TR]
    [TR]
    [TD]Red[/TD]
    [TD]12[/TD]
    [/TR]
    [TR]
    [TD]Blue[/TD]
    [TD]21[/TD]
    [/TR]
    [TR]
    [TD]Green[/TD]
    [TD]19[/TD]
    [/TR]
    </tbody>[/TABLE]
  2. Then RANKX determines the "Value to be Ranked".
    Value to Be Ranked = the 3rd argument (if provided), otherwise the 2nd argument, evaluated in the original evaluation context where RANKX was called. Since no 3rd argument is provided in this case, the 2nd argument is evaluated again, this time in the evaluation context where RANKX was called which is the row context for each row of your original table. Similar to the Expression Evaluated Per Row, we now get Value to be Ranked = 12 (Red rows), 21 (Blue rows) and 19 (Green rows).
  3. Taking a Red row for example, Value to be Ranked = 12, and this is compared against the table from step 1 to determine its rank. Since it is equal to the 3rd highest Expression Evaluated Per Row, the rank = 3. Note: it is possible that Value to be Ranked doesn't appear in the table from step 1, in which case it is inserted into the list of Expression Evaluated Per Row values and then ranked. So if we had Value to be Ranked = 10, RANKX would return 4.
 
Last edited:
Upvote 0
Wow, super helpful.

It was really the "Note:" at the end of 3 that I couldn't follow at all. I had thought that in a Red row we looked at the Red row in the constructed table, but couldn't understand how. I can now see it's actually more subtle than that.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top