Max Rank

[PMF999]

Hi

I have the following table

MeterNo	Value	Rank	Max Rank
123	10	1
123	20	2
123	30	3	MAX
456	10	1
456	20	2
456	30	3	MAX
Table1

Can someone advise me on the formula to return the column Max Rank.

Ideally, I'd like the word MAX to idenify the highest 'Rank' for each 'MeterNo'.

Can you help?

Many thanks in advance!

[AlB · Accepted solution]

Hi @PMF999 

Try this for your calculated column:

MaxRank =
IF (
    Table1[Rank] = CALCULATE ( MAX ( Table1[Rank] ), ALLEXCEPT ( Table1, Table1[MeterNo] ) ),
    "MAX"
)

[PMF99]

Many thanks AIB!