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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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