Conditional formatting for KPI variances

Hi Team,
I used following measure for Actual Vs Budget with formatting in it.

RLU Act Vs Bud =
VAR Currentitem = SELECTEDVALUE(RLUKeyMeasures[Retirement Living Unit])
RETURN
SWITCH(TRUE(),
CurrentItem = “Occupancy” , FORMAT([RLU Act Occupancy%]-[RLU Bud Occupancy%], “0.0%”),
CurrentItem = “Vacant Units”, FORMAT([RLU Act Vacant Units]-1+[RLU Bud Vacant Units], “0”),
CurrentItem = “Vacant units recurrent charges”, FORMAT([RLU Act Vacant Units Recurrent charges]
-1+[RLU Bud Vacant Units Recurrent charges], “0,000”),
BLANK())

I would like to have conditional formatting based on the result. Because it became text, I have to select based on field in conditional formatting.
As per attached highlighted field for variances, how can I add font colour and icon with conditional formatting.

thank you.
Regards,
Aye

Hi @ammu, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Do conditional formatting based on a different column

use the switch to assign a number and then use this value to conditional format. if you have difficulty feel free to load the pbix file and i can do it for you.

thank you

Hello @ammu,

Here’s the article on conditional formatting when we want to format a text.

Hoping this article will be helpful to you and will help to achieve your desired analysis.

Thanks and Regards,
Harsh

Hi C,
Thanks for your kind offer.
If possible, can you please modify my measure to become number.

RLU Act Vs Bud =
VAR Currentitem = SELECTEDVALUE(RLUKeyMeasures[Retirement Living Unit])
RETURN
SWITCH(TRUE(),
CurrentItem = “Occupancy” , FORMAT([RLU Act Occupancy%]-[RLU Bud Occupancy%], “0.0%”),
CurrentItem = “Vacant Units”, FORMAT([RLU Act Vacant Units] -1+[RLU Bud Vacant Units], “0”),
CurrentItem = “Vacant units recurrent charges”, FORMAT([RLU Act Vacant Units Recurrent charges]
-1+[RLU Bud Vacant Units Recurrent charges], “0,000”),
BLANK())

Thank you.

Hello @ammu,

You can convert your measure to a number by placing VALUE() Function ahead of FORMAT().

But you cannot convert your % Formatting to a Number since it will show an error stating as follows -

"Cannot covert value XX.XX% of type Text to type Number. I’ve also attached the screenshot of the error shown while converting to it. For others, you can definitely convert the Text to a Number.

RLU Act Vs Bud =
VAR Currentitem = SELECTEDVALUE(RLUKeyMeasures[Retirement Living Unit])
RETURN
SWITCH(TRUE(),
CurrentItem = “Occupancy” , FORMAT([RLU Act Occupancy%]-[RLU Bud Occupancy%], “0.0%”),
CurrentItem = “Vacant Units”, VALUE(FORMAT([RLU Act Vacant Units] -1+[RLU Bud Vacant Units], “0”)),
CurrentItem = “Vacant units recurrent charges”, VALUE(FORMAT([RLU Act Vacant Units Recurrent charges] -1+[RLU Bud Vacant Units Recurrent charges], “0,000”)),
BLANK())

Hoping this can help you to achieve your desired analysis.

Thanks & Regards,
Harsh

Thanks, Harsh.
it really helps me a lot with VALUE.
It is a shame that we cant format %.
I did work around having a reference measures without any formatting for base on field in conditional formatting.

Regards,
Aye

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up