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

[OMcP]

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. 

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

[Ritaf1983 · Accepted solution]

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:

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

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.

[OMcP]

That works perfectly, thank you Rita! 🙂

[Ritaf1983]

Happy to help🌼