Calculated column showing rank by a summary column

[gazpage]

[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.

 

[Ozeroth]

Something like this should do it.
By the way, in your example shouldn't the ranks be Blue 1, Green 2, Red 3?

Rank =
RANKX ( 
    VALUES ( YourTable[Colour] ),
    CALCULATE ( SUM ( YourTable[Amount] ), ALLEXCEPT( YourTable, YourTable[Colour] ) )
)

 

[gazpage]

Thanks. Will try this. You are right of course about the ranking.

 

[gazpage]

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.

 

[Ozeroth]

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.

 

[gazpage]

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.

 

[gazpage]

I've created a separate post as it's a separate question, but how do you know how I make the formula work with a slicer? I think I would like to replace ALLEXCEPT with ALLEXCEPTSELECTED, but don't think such a function exists.

DAX: How to do ALLSELECTEDEXCEPT()?