Conditional Formatting Quirks

[tobiasmcbride]

Hi,

I have data for forecast and budget. The former is a measure which calculates forecast headcount based not only on whether they are established already but also adds in if they are due to join before a certain date (set by a slider).

I then have the variance as another measure (taking forecast minus budget). All three are displayed in a table like so:

Forecast	Budget	Variance
180	190	-10

I want to have conditional formatting on the table such that when the variance goes between certain values it turns different colours (e.g. -5 to +5 people formatted as green, -10 to -6 as amber, anything absolutely greater than -10 is red).

However when I try to use conditional formatting on PowerBI and input these values in the 'rules' tab, no conditional formatting takes place and it is left without a background colour.

How do I get this to work please?

[MFelix]

Hi @tobiasmcbride ,

I have tried and was abble to setup the visualization be sure that you have place the option to number (by default appears percent) and that you are placing the max and minimun on the correct place.

If you are using a matrix be aware that the condittional formatting doesn't work on total values so if you do not have any row values it considers it's a total line and doesn't returns any formatting.

[tobiasmcbride]

Hi,

I don't see the 'number' or 'percentage' icons for this measure. This is a measure I am using and I am using a table not a matrix. 

[MFelix]

Hi @tobiasmcbride ,

Is your measure in number format? Can you share your measure formula please?

[tobiasmcbride]

Hi,

I can't share the measure formula just now but essentially it calculates the rows which have certain text values, takes away the sum of the budget. That is then filtered (by slicers) by date and which estimate (final or provisional) we have.

When I head to the data conditional formatting tab for that measure (forecast variance) I can enter values but no 'number or percentage' type appears. 

Again this is for a visual table that I am creating and want to show for. 

[MFelix]

Then it's because your measure is returning a text value and not a number value.

check the format of the measure to see if it's as a number or text please.

[tobiasmcbride]

Hi 

I have checked and the measure is formatted as a number and still not working. 

[MFelix]

Hic @tobiasmcbride ,

Witouth any example is difficult to give you a better explanation, can you share some mockupdata and the measure you are using to calculate the Variance?

If this is sensitive information you canshare it trough private message or with mockup values, as I refered to you I have made some test and was abble to make the condittional formatting but my model for sure is not equal to yours is just a simple column with forecast and budget columns.

[tobiasmcbride]

OK sure thing,

So the measure I have for variance is as follows:

Forecasted Variance = (Calculate(COUNTROWS(Headcount), Headcount[Current Status] = "Established") + (Calculate(COUNTROWS(Headcount), Headcount[Current Status] = "New Starter")) - SUM(HCBudget[HC Value])

This then generates the difference in absolute terms between the total of Established and New Starter employees minus the value in the budget column.

I then have 2 slicers for the budget year and type (e.g. 2019 provisional) and the specific dates we are looking at. 

[MFelix]

Hi @tobiasmcbride ,

I have checked the information you have gave me and based on the post below I have understood what is happening.

https://community.powerbi.com/t5/Desktop/Conditional-formatting-not-working-on-measure/td-p/621171

Measure are based on context so when you have the calculation of the measure without reference to the year you are filtering it will not return the background colour.

Be aware that this is because of the table visualization since you are adding additional levels of context. If you use the condittional formatting on a card since the context is based on the slicer and on the card it will work normally.

Not sure if this fits your needs but if you place a column with the year on your table that will return the condittional formatting as you need.

You can hide the column of the year by reducing it as much as possible. I'm assuming that you will not select more than 1 year.

See attach the PBIX file.(on the file the totals are turn on but it's not necessary to have then turn on.

[tobiasmcbride]

I then take this variance data and put it in a visual table for the report with the forecast value (which is the first part of the H/C variance essentially) and compare it with the budget figure. 

Forecast	Budget	Variance
180	190	-10

It is the latter column on the table in the report (visual table) that I want conditionally formatted and it refuses to do so depsite entering the correct rule set. 

[Anonymous]

Hi @tobiasmcbride ,

Whether your problem has been resolved?

Best Regards

Rena

[amitchandak]

You can create a measure that returns color and have to use it for each column of matrix

color measure = if([variance] <10,"red","green")

And then go to conditional formatting /background color, choose a color and hover on the color, you will see tree dot and option for conditional formatting. Use field option and this measure

Refer

https://medium.com/@raghu.949/conditional-formatting-of-matrix-visual-in-power-bi-9d37dd70c52e

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin