Help with RANK function with and dynamic formatting

[Fusilier]

Hi,

Two parts to this question:

1. I have data that is based on responses where the respondent is presented with a list of options and selects either Yes or No depending on whether the option is important to them.

Data of a count of all the 'YES' responses is currently shown in a table:

I've been trying to add a ranking column to the table without much success.

The measure I've been trying to do this with is:

Rank- Important =

RANK(DENSE, ALLSELECTED('Combined Table'[Answer]))

Can sombody point out what I've done wrong with this measure please?

N.B. The table could be filtered by any number of different filters (e.g. age group or location) so the ranking would need to just be for the rows currently shown in the table 

2. I then want to be able to dynamically color the text of the top three ranked rows in a different color to the other rows.

I presume I would just be if the rank value was <=3

Hope somwbody can point me in the right direction?

[SamWiseOwl · Accepted solution]

Hi @Fusilier 

Create this as a new measure, make sure to put your own table and column names in

Rank Questions =
RankX( --Performs an expression for each row
        AllSelected('Combined Table'[Question]) --Remove internal filters keeps external
        ,Calculate(Count(Combined Table'[Answer])) --Calculate applies a fitler

        ,DESC --Sort most to least

        ,Skip--If 2 questions rank 1st, third question gets 3nd not 2nd
)

Apply this measure in Cell elements  click the Fx symbol and change the Format Style to Field value
Colour rank =
If( [Rank Questions] <= 3, "Green", "Red")

[Fusilier]

Thank you. Works great!