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!

[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".

[123abc · Accepted solution]

To rank your 5 measures and format the card visual in Power BI, you can follow these steps:

Step 1: Combine Your Measures into a Table

Since Power BI doesn’t allow direct ranking of individual measures, you need to consolidate them into a single calculated table or column.

1. Create a table with your measures using the UNION function. In Power BI, go to Modeling > New Table, and enter the following DAX formula:

   MeasureTable = UNION( ROW("Measure Name", "Top 5%", "Value", [top 5%]), ROW("Measure Name", "Top 10%", "Value", [top 10%]), ROW("Measure Name", "Top 15%", "Value", [top 15%]), ROW("Measure Name", "Top 20%", "Value", [top 20%]), ROW("Measure Name", "Bottom 80%", "Value", [bottom 80%]) )

   Replace [top 5%], [top 10%], etc., with your actual measures.

2. This creates a table with two columns: Measure Name and Value.

---

Step 2: Rank the Measures

Create a new calculated column in the MeasureTable to rank the measures by their values.

1. Go to Modeling > New Column and enter the following DAX formula:

   Rank = RANKX(ALL(MeasureTable), MeasureTable[Value], , DESC, DENSE)

   This will rank the measures from the highest to the lowest value.

---

Step 3: Assign Colors Based on Rank

Create another calculated column to assign colors based on the rank.

1. Go to Modeling > New Column and enter the following DAX formula:

   Color = SWITCH( MeasureTable[Rank], 1, "#006400", -- Dark Green 2, "#00FF00", -- Light Green 3, "#FFFFFF", -- White 4, "#FF6347", -- Light Red 5, "#8B0000", -- Dark Red "#000000" -- Default (Black) )

---

Step 4: Apply Conditional Formatting to the Card Visual

1. Add the MeasureTable[Value] column to a card visual.
2. In the Format pane of the visual, go to Data Label > Conditional Formatting > Field Value.
3. Set the Based on field to MeasureTable[Color].

This will apply the corresponding colors to each measure in your card visual.

---

Alternate Method: Display All Values in One Visual

If you want to display all the measures together, use a table or matrix visual:

1. Add Measure Name, Value, and Color to the table visual.
2. Apply conditional formatting to the Value column using the Color field.

This setup will allow you to dynamically rank and format your measures as per their values.

AND below  is alternatiove method :

Combine Your Measures into a Table

Since Power BI doesn’t allow direct ranking of individual measures, you need to consolidate them into a single calculated table or column.

1. Create a Table with Your Measures:
   • Go to Modeling > New Table.
   • Enter the following DAX formula:

MeasureTable = 
UNION(
    ROW("Measure Name", "Top 5%", "Value", [top 5%]),
    ROW("Measure Name", "Top 10%", "Value", [top 10%]),
    ROW("Measure Name", "Top 15%", "Value", [top 15%]),
    ROW("Measure Name", "Top 20%", "Value", [top 20%]),
    ROW("Measure Name", "Bottom 80%", "Value", [bottom 80%])
)

• Replace [top 5%], [top 10%], etc., with your actual measures.

This creates a table with two columns: Measure Name and Value.

Step 2: Rank the Measures

Create a new calculated column in the MeasureTable to rank the measures by their values.

1. Create a New Column:
   • Go to Modeling > New Column.
   • Enter the following DAX formula:

Rank = RANKX(ALL(MeasureTable), MeasureTable[Value], , DESC, DENSE)

This will rank the measures from the highest to the lowest value.

Step 3: Assign Colors Based on Rank

Create another calculated column to assign colors based on the rank.

1. Create a New Column for Colors:
   • Go to Modeling > New Column.
   • Enter the following DAX formula:

Color = 
SWITCH(
    MeasureTable[Rank],
    1, "#006400",  -- Dark Green
    2, "#00FF00",  -- Light Green
    3, "#FFFFFF",  -- White
    4, "#FF6347",  -- Light Red
    5, "#8B0000",  -- Dark Red
    "#000000"      -- Default (Black)
)

Step 4: Apply Conditional Formatting to the Card Visual

1. Add the MeasureTable[Value] Column to a Card Visual:
   • In the Format pane of the visual, go to Data Label > Conditional Formatting > Field Value.
   • Set the Based on field to MeasureTable[Color].

This will apply the corresponding colors to each measure in your card visual.

Alternate Method: Display All Values in One Visual

If you want to display all the measures together, use a table or matrix visual:

1. Add Measure Name, Value, and Color to the Table Visual.
2. Apply Conditional Formatting to the Value column using the Color field.

This setup will allow you to dynamically rank and format your measures as per their values.

Feel free to ask if you have any questions or need further assistance! 😊

[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. 

[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.