PowerBi DAX Background colour: Conditonal formatting for dynamic measures with same total

[DAXmnbk]

Hi frens, 

I was wondering if someone has ever done Dynamic conditional formatting based on dynamic values of measures. 

I have sales data for the different cities and I want dynamic color changes based on total sales amount. 

To be more specific what I am looking for is upon changing city, the first highest sale amount should stay Red, the second highest should stay blue, the third highest should stay yellow and the least product should stay orange. 

Your help would be much appreciated. 

[Dangar332]

hi, @DAXmnbk 

use below measure for conditional formatting

Measure = 
var a = RANKX(ALL('Table'[value]),'Table'[value],MIN('Table'[value]))
return
SWITCH(TRUE(),
a=1,"#ff0000",
a=2,"#0000FF",
a=3,"#FFFF00",
a>3,"#FFA500"
)

below my data to test your requirnment

for measure in conditional formatting

click on column name and click on conditiona formatting and click on background color

2.  change format style to field value and select measure from table and click on ok

[DAXmnbk]

Hi Dangar332 thanks for replying, the values of A, B, C, and D are counted measures, and was wondering if there is a way of doing this thing with 4 different counted measures? 

Cheers. 

[Anonymous]

That sounds like a neat idea to visually represent sales data! To achieve dynamic color changes based on the total sales amount for different cities, you can use conditional formatting in spreadsheet software like Excel or Google Sheets.

Here’s a general step-by-step approach:

1. Sort the Data: Arrange your sales data in descending order for each city based on the total sales amount.

2. Conditional Formatting: Use conditional formatting rules to assign colors to the cells containing the sales data.

   • For the first highest sale amount, apply the "Red" color.
   • For the second highest sale amount, apply the "Blue" color.
   • For the third highest sale amount, apply the "Yellow" color.
   • For the least product, apply the "Orange" color.

3. Apply Relative References: Ensure that the conditional formatting rules use relative references, so they adjust dynamically when you switch cities or when the data changes.

This way, whenever you switch to a different city or update the sales data, the colors will automatically adjust based on the new sales amounts for that specific city.

[DAXmnbk]

Chwayita-Momoza thanks for replying, I was wondering if someone has written a Power BI background colour DAX measure for this issue before.