Regarding Conditional Formatting across columns (Using RANKX/TOPN or alternatives)

[sarveshkalyan]

I'm having trouble in color coding the values across different columns,

I have 4 metrics(Metric 1,2,3,4) from four different table(Table 1,2,3,4), a calendar table and another table(Table) with just 1 column Metric that has text names of these metrics, i then use switch case to get the values populated as follows, 

suppose this is the data

Metrics	2020	2021	2022
Metric 1	200	300	100
Metric 2	500	210	540
Metric 3	300	320	213
Metric 4	100	800	600

I would like to get the result like this, get conditional color across different rows(here, the color intensity is based on the highes to lowest of each metric across the years, ie each row is independant) i believe this is possible through field value in background color > cell elements(and i thought of creating a rank measure to specify the context and i am stuck now)

and this is the DAX i have used so far

Rank =

IF(SELECTEDVALUE(Table[Metrics])="Metric 1",

RANKX(

ALLSELECTED('Table1'[Metric 1]),

DISTINCTCOUNT('Table1'[Metric 1]),

BLANK())

and the value i am getting is as follows

Fiscal Year	2020	2021	2022
Metrics	Value	Rank	Value	Rank	Value	Rank
Metric 1	200	1	300	1	100	1
Metric 2	500		210		540
Metric 3	300		320		213
Metric 4	100		800		600

Please let me know if this is possible, i think if i get the rank to work, conditional colors can be easily added.

Thanks, 

Sarvesh 

[v-yiruan-msft · Accepted solution]

Hi @sarveshkalyan  ,

Please update the formula of your measure [Rank] as below and check if you can get the rank values:

Rank =
IF (
    SELECTEDVALUE ( Table[Metrics] ) = "Metric 1",
    RANKX (
        ALLSELECTED ( 'Table1'[Metric 1] ),
        CALCULATE ( DISTINCTCOUNT ( 'Table1'[Metric 1] ) )
    ),
    BLANK ()
)

In addition, you can refer the following links to resolve the problem.

Ranking is not showing correctly on card

rank = RANKX(ALL('Table'[Name]),CALCULATE(SUM('Table'[Sales])),,DESC,Dense)

Showing Ranking in a Visual in Power BI using RANKX DAX function

If the above one can't help you get the desired result, please provide some sample data in your tables "Table"and "Table1" (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards