Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Venki5080
Regular Visitor

Need help with ranking 5 measures

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!

6 REPLIES 6
Venki5080
Regular Visitor

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

Venki5080
Regular Visitor

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
Community Champion
Community Champion

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

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

Kedar_Pande
Super User
Super User

@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
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.