Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe 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.
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!
I created the table using Union and row functions. Created a rank calculated column and then created the color measure.
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?
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.
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".
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.):
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()
)
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
)
2. Apply Conditional Formatting
To format the card visual dynamically:
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. Final Steps
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |