Need help with ranking 5 measures

[Venki5080]

Hi All,

I am a newbie to Power BI. Just using it from past 3 months.

I have web data with impressions, clicks for several campaigns. I have created 5 mesures to see the top 5%, top 10%, top 15%, top 20% and bottom 80% values which calculate the contribution of top campaigns to total impressions like below:

top 5% = (sum of impressions of top 5 campaigns)/total impressions.

Similarly calculated for other measures.

I have added these 5 measures to Card(New) to visualize all the 5 values. I want to format these 5 measures as per their values like below.

1st highest value - Dark Green
2nd Highest value - light green

3rd highest value - white

4th highest value - light red

5th highest value - dark red

I thought of creating a measure to rank these 5 measures and to format the card visual using "Field Value" formatting. But I am not able to create a measure to rank these 5 top % measures. Please help me to rank these measures. Any help would be highly benefecial. Thanks!

[Venki5080]

I created the table using Union and row functions. Created a rank calculated column and then created the color measure. 

[Venki5080]

Thanks for the reply. Solution worked all fine until the very last step. When I use the formatting on the card visual with field value as MeasureTable[Color], it is only taking "Green" for every measure. Any idea why this could be happening?

[123abc]

which method is you are using ... 1st and 2nd.

[Venki5080]

I created the table using Union and row functions. Created a rank calculated column and then created the color measure. 

[Kedar_Pande]

@Venki5080 

Create a disconnected table, e.g., MeasureNames = {"Top 5%", "Top 10%", "Top 15%", "Top 20%", "Bottom 80%"}.

Link Measures to Table:

MeasureValues =
SWITCH(
SELECTEDVALUE(MeasureNames[Name]),
"Top 5%", [Top 5%],
"Top 10%", [Top 10%],
"Top 15%", [Top 15%],
"Top 20%", [Top 20%],
"Bottom 80%", [Bottom 80%]
)

Rank the Measures:

MeasureRank =
RANKX(
ALLSELECTED(MeasureNames),
[MeasureValues],
,
DESC
)

Apply Conditional Formatting:

MeasureColor =
SWITCH(
[MeasureRank],
1, "#006400",
2, "#90EE90", 
3, "#FFFFFF",
4, "#FF7F7F", 
5, "#8B0000", 
"#FFFFFF" 
)

Apply the MeasureColor in the Card visual using the "Field Value" option under "Data Label".

[grazitti_sapna]

Hi @Venki5080 ,

To rank the five measures and apply the desired formatting in Power BI, you can follow these steps:

1. Create a Measure to Rank the Top % Measures
In Power BI, measures cannot directly interact with each other like columns do. However, you can calculate ranks based on the values of your measures using a disconnected table and some DAX logic.

To rank the five measures and apply the desired formatting in Power BI, you can follow these steps:

1. Create a Measure to Rank the Top % Measures
In Power BI, measures cannot directly interact with each other like columns do. However, you can calculate ranks based on the values of your measures using a disconnected table and some DAX logic.

Step 1.1: Create a Disconnected Table
Create a disconnected table to list your measures (e.g., Top 5%, Top 10%, etc.):

1. Go to Home > Enter Data.
2. Enter the following rows in a new table called Measure Table:

   • Measure Name
     Top 5%
     Top 10%
     Top 15%
     Top 20%
     Bottom 80%

Step 1.2: Create a Measure to Retrieve Values
Write a measure to dynamically retrieve the value of each measure in the Measure Table:

• Measure Value = 
  SWITCH(
  SELECTEDVALUE('Measure Table'[Measure Name]),
  "Top 5%", [Top 5%],
  "Top 10%", [Top 10%],
  "Top 15%", [Top 15%],
  "Top 20%", [Top 20%],
  "Bottom 80%", [Bottom 80%],
  BLANK()
  )

• This measure will dynamically return the value of the selected measure based on the Measure Table.

Step 1.3: Create a Ranking Measure
Now, create a measure to rank these measures based on their values:

• Measure Rank = 
  RANKX(
  ALL('Measure Table'),
  [Measure Value],
  ,
  DESC
  )

• This measure will rank the measures based on their values in descending order (higher values get a lower rank).

2. Apply Conditional Formatting
To format the card visual dynamically:

1. Go to the Card Visual with your measures.
2. Add a new measure for formatting. For example:

   • Formatting Color = 
     SWITCH(
     [Measure Rank],
     1, "#006400", -- Dark Green
     2, "#32CD32", -- Light Green
     3, "#FFFFFF", -- White
     4, "#FF6347", -- Light Red
     5, "#8B0000", -- Dark Red
     "#000000" -- Default (Black)
     )

3. Select the Card Visual, go to the Format pane > Data Label, and under Conditional Formatting, select Field Value and point it to the Formatting Color measure.

3. Final Steps

• If you’re displaying all 5 measures simultaneously, create a table or matrix using the Measure Table and add the Measure Value measure as a column.
• The ranks and colors will adjust dynamically based on the values of the measures.

This approach provides a scalable and dynamic solution for your use case. Let me know if you encounter any challenges!

I hope the provided solution works for you

If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.