Conditional Formatting - top 3

[Anonymous]

I have a report that is like a league table. Is it possible to have, for a given column, conditional formatting rules that will highlight the top 3 values within this column? Actually I want each 3 values to be a slightly different colour to distinguish between the best, 2nd best and 3rd best in that column.

I intend to do similar rules to many columns, and having all rows rather than just the top 3 in each column formatted doesn't look nice. Having a "top 3" is the key thing but the formatting rules don't appear to allow it.

[DataInsights · Accepted solution]

@Anonymous, try this:

1. Create the following measures:

Category Rank = 
RANKX ( ALL ( TestData[Category] ), CALCULATE ( SUM ( TestData[Amount] ) ), , ASC, Dense )

Category Rank Color = 
SWITCH ( [Category Rank],
1, "#CDE7FF",
2, "#72BCFF",
3, "#1B92FF"
)

2. Add [Category Rank] (and other fields) to a matrix.

3. Add conditional formatting to [Category Rank] as follows: format by Field value, based on field Category Rank Color.