cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.





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 other members 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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 48 members 865 guests
Please welcome our newest community members: