cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kwpbi Regular Visitor
Regular Visitor

Conditional formatting with rules... base maximum off of table value?

Again, I am trying to do something that should be simple, but I cannot figure out how to do it in Power BI. I am using a gauge-style visual with conditional formatting for the data color. I want to set it up with two rules: for values between zero and the "target value", I want it red. For values above the "target value", I want it green. Seems pretty straight-forward for this type of visual.

However, those fields for min/max seem to only accept numerical inputs. I need to drive these formatting rules with the "target value" from my table, because it changes from day to day.

Is this possible?

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Conditional formatting with rules... base maximum off of table value?

@kwpbi  create a measure for color like

 

KPI Color = IF( [Value] < [Target] , "Red", "Green" )

in conditional formatting, choose field value and select kpi color measure






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

6 REPLIES 6
Super User
Super User

Re: Conditional formatting with rules... base maximum off of table value?

@kwpbi  create a measure for color like

 

KPI Color = IF( [Value] < [Target] , "Red", "Green" )

in conditional formatting, choose field value and select kpi color measure






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

kwpbi Regular Visitor
Regular Visitor

Re: Conditional formatting with rules... base maximum off of table value?

Thanks, Parry2k, this is almost what I need... I think we need to take one step further?

 

The [value] that I am reporting is a sum from a visual-level, relative-date filter. Can you think of a way to make this work when that [value] is not coming from a table?

Super User
Super User

Re: Conditional formatting with rules... base maximum off of table value?

@kwpbi you can create measure and use that in this if condition. Am I missing something here?






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
kwpbi Regular Visitor
Regular Visitor

Re: Conditional formatting with rules... base maximum off of table value?

No, I don't think you are... I am new to Power BI and still learning how to use measures as variables and sub-routines (this is so different from Excel and VBA!).

 

I think you have pointed me in the right direction... time to go home for the day though. I will let you know tomorrow if I need more help.

 

Thanks again!

Community Support Team
Community Support Team

Re: Conditional formatting with rules... base maximum off of table value?

Hi @kwpbi ,

 

We can set condiftional formatting by filed value based on measure like this.

Measure 2 = var a = SUM('FACT TABLE'[value])
return
IF(a>='target table'[Target var],"#01B8AA","#FD625E")

Before that, we can create a target measure as below.

 

 

Target var = var maxd = CALCULATE(MAX('target table'[date]),ALLSELECTED('target table'))
return
CALCULATE(MAX('target table'[target]),FILTER('target table','target table'[date] = maxd))

 

Then we can get the result as below by table visual.

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
kwpbi Regular Visitor
Regular Visitor

Re: Conditional formatting with rules... base maximum off of table value?

It took some trial and error, but I got this to work using a series of measures for each scenario. Here is what I came up with for my final code:

 

WEEK-TAR =
VAR WETAR =
    SUM('Sales Visuals Data'[Weekly Target])
RETURN WETAR
 
WEEK-COLOR =
VAR WECOL =
    IF(SUM(YTD_Invoices[INVOICEAMOUNT])>='Sales Visuals Data'[WEEK-TAR],"GREEN","RED")
RETURN WECOL

 

MONTH-TAR =
VAR MOTAR =
    SUM('Sales Visuals Data'[Monthly Target])
RETURN MOTAR
 
MONTH-COLOR =
VAR MOCOL =
    IF(SUM(YTD_Invoices[INVOICEAMOUNT])>='Sales Visuals Data'[MONTH-TAR],"GREEN","RED")
RETURN MOCOL

 

2019-YTD-TAR =
VAR YTDTAR =
    SUM('Sales Visuals Data'[2019 YTD Target])
RETURN YTDTAR
 
YTD-COLOR =
VAR YTDCOL =
    IF(SUM(YTD_Invoices[INVOICEAMOUNT])>='Sales Visuals Data'[2019-YTD-TAR],"GREEN","RED")
RETURN YTDCOL
 
Thanks again for your help!

 

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,967)