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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
OMcP
Regular Visitor

How to base the conditional formatting of a column on the individual column and not all columns

I have a matrix with country names along the side, and categories across the top. Each of these country - category combinations has a calculated percentage as the value. I've applied the background colour in the cell element, but it's basing the colour of the entire set of columns, and not of that categroy columns subtotal. This causes an issue as for example, 8% in one category is a very good result, while 67% in another category is a bad result. But the 8% shows up as dark red, while the 67% is green. Is there a way for me to base the background colour of the subtotal of each individual column category? 

 

So for example, Column A would have a subtotal of 63%, a value of 44% would be red. Column B would have a subtotal of 7%, a value of 9% would be green. I attached a rough mockup screenshot to show what my intended goal is, vs what i currently have.  Screenshot 2025-01-10 160831.pngScreenshot 2025-01-10 161417.png

Hope this all makes sense, any help would be greatly appreciated. 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @OMcP 
You need to create a ranking measure based on category, like :

Rank inside category =
RANKX(ALLSELECTED('Categories table'),[your measure],,)
And then base conditional formatting on this measure:
Ritaf1983_0-1736543868888.png

I Made an example for one of my blog posts with other data :

Ritaf1983_1-1736543916254.png

The pbix with the example is attached
You can also take a look at my old blog post about heat map ( there is a row logic there but it is the same, just change the DAX of ranking to the columns level )

https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/When-to-Use-Heatmaps-and-How-to-Im...

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

3 REPLIES 3
Ritaf1983
Super User
Super User

Hi @OMcP 
You need to create a ranking measure based on category, like :

Rank inside category =
RANKX(ALLSELECTED('Categories table'),[your measure],,)
And then base conditional formatting on this measure:
Ritaf1983_0-1736543868888.png

I Made an example for one of my blog posts with other data :

Ritaf1983_1-1736543916254.png

The pbix with the example is attached
You can also take a look at my old blog post about heat map ( there is a row logic there but it is the same, just change the DAX of ranking to the columns level )

https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/When-to-Use-Heatmaps-and-How-to-Im...

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

That works perfectly, thank you Rita! 🙂

Happy to help🌼

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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