Max of Rank Measure

[llealsantos]

I have the following measure to conditional format a table.

Measure 2 =

VAR Periods = ALL('Calendar'[Year Period])

VAR Total = [Laundry]

VAR Ranking = RANKX(Periods, [Laundry], Total, DESC)

VAR Result = IF(Total = 0, 0, IF(Ranking <= 5, 1, 0))

RETURN

Result

This ranking is descending, meaning that the lowest values for laundry on the selected periods will be ranked the highest.
If a number in the ranking is below 5 it will return 1 and I will color it in red.

I now want the top 3 values in the rank to return 2 so I can colour it in green.
My first thought was to do this:

Measure 2 =

VAR Periods = ALL('Calendar'[Year Period])

VAR Total = [Laundry]

VAR Ranking = RANKX(Periods, [Laundry], Total, DESC)

VAR Result = IF(Total = 0, 0, IF(Ranking <= 5, 1,

                IF(AND(Ranking >= 12, Ranking <= 14), 2, 0)))

RETURN

Result

I have 14 periods on the table so the max would always be 14 and if I want the bottom 3 it would be from number 12 to 14.

However, this will not work if I have duplicated values for different periods, because the max will no longer be 14.

So I thought about calculating the maximum value that I have on the rank and subtracting 3 to have the bottom 3 values, but I can't figure out how to get the Max of the rank.

[hackcrr · Accepted solution]

Hi, @llealsantos 

You can do this by using this MAXX function to iterate through the cycles and find the maximum ranking. You can do this by modifying the metric in the following ways:

Measure 2 =
VAR Periods = ALL('Calendar'[Year Period])
VAR Total = [Laundry]
VAR Ranking = RANKX(Periods, [Laundry], Total, DESC)

// Calculate the maximum rank
VAR MaxRank =
    MAXX(
        Periods,
        RANKX(Periods, [Laundry], [Laundry], DESC)
    )

// Determine the result based on the rank
VAR Result =
    IF(
        Total = 0,
        0,
        IF(
            Ranking <= 5,
            1,
            IF(
                Ranking <= (MaxRank - 2), // Top 3 values
                2,
                0
            )
        )
    )

RETURN
    Result

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.