How to see the top 3 and the bad 3

[Milozebre]

Hello Community,

Is it possible to see in a column like below The 3 best and the 3 worst with.

Thank you

[luapdoniv · Accepted solution]

This is the closest I could get:

[v-lionel-msft · Accepted solution]

Hi @Milozebre ,

Try to do like this:

1. creating a calculated column: 

Rank = 
RANKX(
    'Sheet1 (2)',
    [quantity],
     , ASC, Dense
)

2. creating a measure:

Color = 
VAR x = 
CALCULATE(
    MAX([Rank]),
    ALL('Sheet1 (2)')
)
RETURN
IF(
    MAX([Rank]) >= 1 && MAX([Rank]) <= 3,
    "red",
    IF(
        MAX([Rank]) <= x && MAX([Rank]) >= x-2,
        "green"
    )
)

3. adding the measure to "Conditional formatting->Background color":

Best regards,
Lionel Chen

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

[Milozebre]

Hil @v-lionel-msft , 

Thank you very much for your reply. 

Its perhaps a stupid question but what's "sheet1 (2)"

[luapdoniv]

Can you please confirm you want to see them highlighted or only see the top3 and bottom3 there ignoring the rest of the data rows.

[Milozebre]

Hello @luapdoniv , 

Thank you for the reply. 

I want to see the same view like that.

If not, only the highlighted 🙂 

Thnak you in advance

[luapdoniv]

This is the closest I could get.
Highlight top 3 values by conditional format as background color and highlight bottom 3 values by conditional format as font color
OR
Highlight top 3 values by conditional format as background color and highlight bottom 3 Names by conditional format as background color

Will continue R&D to see if your requirement can be achieved. Let me know if this helps.

[luapdoniv · Accepted solution]

This is the closest I could get: