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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Sjoerd_g
Frequent Visitor

Conditional formatting continued on second visual

Hey all,

 

I have a Top and a Bottom 5 table for branches and their social review ratings.

 

I am using a DAX formula to have conditional formatting per matrix column (which does not work perfectly for Facebook ratings as you can see, but that's an issue for another time), and I would like the tables to have continuous conditional formatting. 

 

What I currently have:

Sjoerd_g_0-1654185012662.png

This makes it look like the 3.9 average rating on the bottom 5 is a good rating, but in fact, it is much lower than the 4.50 which shows red on the Top 5 visual.

 

I am expecting to see the Top 5 mostly shades of green, and the bottom 5 mostly shades of orange/red.

 

The DAX I am currently using:

 

Formatting Review Score Branch = 

RANKX(

ALLSELECTED(' filters'[Branch]),

[Review Rating],

[Review Rating],

ASC

)

 

There is a bit more to this though, as I have filtered out branches with less than 5 reviews using the following filter (because I am using Top N on the visual for selecting the most recent business period):

RANKX(
FILTER( ALLSELECTED( 'filters'[Branch] ), [Social reviews] >= 5 ),
[Review Rating],
[Review Rating],
DESC, Dense
)
 
I can't do ALL branches, because there are multiple companies in the dataset. In the 'filters' table, there is also a [company] column if that helps.
 
Any help is appreciated. Thanks
1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Sjoerd_g 

 

You should format the background color based on the exact Rating values rather than dynamic ranking values. Specify a pair of fixed minimum and maximum values (e.g. 0 and 5) for conditional formatting instead of using the default Highest and Lowest values. 

vjingzhang_0-1654679876660.png

This should work. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Sjoerd_g 

 

You should format the background color based on the exact Rating values rather than dynamic ranking values. Specify a pair of fixed minimum and maximum values (e.g. 0 and 5) for conditional formatting instead of using the default Highest and Lowest values. 

vjingzhang_0-1654679876660.png

This should work. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.