cancel
Showing results for 
Search instead for 
Did you mean: 
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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors