Forum Discussion

Fusilier's avatar
Fusilier
Icon for Helper III rankHelper III
2 years ago
Solved

Help with RANK function with and dynamic formatting

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?
 
 
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    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")

     

     

2 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    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")