cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Conditional Formatting for negative values with 3 conditions

Hi Experts,

I want to do the conditional formatting of negative values as mentioned below. How can I achieve it with icons.

also , please please let me know how to work with numbers or percentages while doing the conditional formatting

 Condition Icon Greater than or equal to -100 but less than or equal to -5 Green Icon Greater than -5 but leass than or equal to 0 Yello icon Greater than 0 but less than or equal to 100 Red Icon

11 REPLIES 11
Community Support

You could refer to @speedramps 's suggestions, or set conditional formatting like below

Best Regards,
Zoe Zhi

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

Super User

Create this measure and drag and drop it to your visual.

the UNICHAR will create the icon for you.

Myicon =
VAR Myvalue = -4     -- substitute this hard code value with you measure and multiply with 100 if a %
RETURN
SWITCH(TRUE(),
Myvalue < -100, BLANK(),
Myvalue <= -5, UNICHAR(128994), -- green if > -100 and <= -5
Myvalue <= -0, UNICHAR(128993), -- yellow if > -5 and <= 0
Myvalue <= 100 , UNICHAR(128308), -- red if >0 and <=100
BLANK()
)

Helper III

@speedrampsI tweaked the formula, but it still gives me the same result. Sorry but I am quite new to this

Here is the DAX that i used

Myicon2 =
VAR MYVALUE = [Difference %] * 100
RETURN
SWITCH(TRUE(),
MYVALUE < -100, BLANK(),
MYVALUE <= -5, UNICHAR(128994),
MYVALUE <= -0, UNICHAR(128993),
MYVALUE <= 100, UNICHAR(128308),
BLANK()
)
Helper III

@speedramps  Thank you. I added the measure provided by you but it only gives me the 'red' icons only. Below is the screenshot

Here is the DAX

MyICON =
VAR MYVALUE = [Difference %]
RETURN
SWITCH(TRUE(),
MYVALUE<-100,BLANK(),
Myvalue <= -5, UNICHAR(128994), -- green if > -100 and <= -5
Myvalue <= -0, UNICHAR(128993), -- yellow if > -5 and <= 0
Myvalue <= 100 , UNICHAR(128308), -- red if >0 and <=100
BLANK()
)
Super User

In my instructions it did say ...
VAR Myvalue = -4 -- substitute this hard code value with your measure and multiply with 100 if a %

but unfortunately you have done this
VAR MYVALUE = [Difference %]

[Difference %] has a percent datatype so 13% will be stored as 0.13

and therefore 0.13 is less than 5

If you tweak it will work.

Super User

Try this

``````MyICON =
VAR MYVALUE = [Difference %]
VAR RESULT =
IF (
MYVALUE > -1
&& MYVALUE <= -0.05,
UNICHAR ( 128994 ),
IF (
MYVALUE > -0.05
&& MYVALUE <= 0,
UNICHAR ( 128993 ),
IF (
MYVALUE > 0
&& MYVALUE <= 100,
UNICHAR ( 128308 ),
BLANK ()
)
)
)
RETURN
RESULT``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Helper III

Thanks for your reply. I used your formula but its showing me below results still (only red icons appear)

Super User

Create a DAX measure for an icon.

``Icon = UNICHAR(11044)``

Use this measure in your table visual.

Create another measure for conditional formatting.

``````Color =
VAR MYVALUE = [Difference %]
VAR RESULT =
IF (
MYVALUE > -1
&& MYVALUE <= -0.05,
"green",
IF (
MYVALUE > -0.05
&& MYVALUE <= 0,
"orange",
IF (
MYVALUE > 0
&& MYVALUE <= 100,
"red",
BLANK ()
)
)
)
RETURN
RESULT``````

Then format the Icon (Font) measure field using the newly created "Color" measure.

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Helper III

@nandukrishnavs- Superb, That worked perfectly. Many Thanks.

Super User

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Super User

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors