My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Conditional Formatting - PowerBI DAX|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Conditional Formatting - PowerBI DAX|Dashboards & Charts|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope



Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_PrintTopic sp_TopicIcon
Conditional Formatting - PowerBI DAX
Avatar
Glenisa Tumbaga
Member
Members
Level 0
Forum Posts: 6
Member Since:
June 27, 2022
sp_UserOfflineSmall Offline
1
September 15, 2022 - 5:35 am
sp_Permalink sp_Print

Hello,

I am currently trying to apply some conditional formatting on my powerbi dashboard. However, it doesn't seem like it's accepting negative percentage when I go through the 'conditional formatting' option. So what I have done so far is I wrote a DAX formula which consists of 'if' statements. 

So what I would like to happen is:

anything is at 0 variance or higher = green
within 5% of goal = yellow
below 5% of goal = red

So I wrote a Dax - then reference this DAX formula to apply a conditional formatting.
CF =
IF(
[Variance 1(%)] < -0.05, 1,
IF(
[Variance 1(%)] <-.04, 2,
IF(
[Variance 1(%)] >= 0,
3)))
 
Specifically for the second formula - I need numbers mark as yellow if it's between certain numbers. So basically between -0.5% and 0. I keep getting errors Cry
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1530
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
September 15, 2022 - 1:07 pm
sp_Permalink sp_Print

Hi Glenisa,

If you supplied your file that would help to see your measure and data, but without it I can only give an example of this formatting.

The DAX to create the colours for the CF can be this

 

Variance 1(%) =

VAR _val = MAX('DataTable'[Value])

RETURN

SWITCH ( TRUE(),

_val >= 0 , "Green",

_val >= -0.05 , "Yellow",

"Red"

)
 
Which gives this
 
power bi conditional formattingImage Enlarger
 
Check my attached file to see the data and code I've used.
 
Regards
 
Phil
 
Avatar
Glenisa Tumbaga
Member
Members
Level 0
Forum Posts: 6
Member Since:
June 27, 2022
sp_UserOfflineSmall Offline
3
September 23, 2022 - 6:23 am
sp_Permalink sp_Print

Is there a way for me to do apply a conditional formatting if a value falls into a certain range. 

For example:

If value >= -5% percent and < 0% then it YELLOW

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1530
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
September 23, 2022 - 12:20 pm
sp_Permalink sp_Print

Yes, my code already does that.  Have you tried using it?

regards

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Aminu Sule
Samuel Ramirez
Abdifatah Osman
mohad borhom
Abayomi Adedeji
Ganesh MVS
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Forum Stats:
Groups: 3
Forums: 24
Topics: 6364
Posts: 27828

 

Member Stats:
Guest Posters: 49
Members: 32346
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen