cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User II
Super User II

Customized Conditional Formatting (Using DAX)

Hi,

 

I am working on designing a solution and need help.

 

There are two facts table: Data and Target.

 

For certain KPIs, I need to compare Yearly/Monthly/Daily actual vs target. I am looking for applying conditional formatting using DAX. I am partial successful in the case if all the target comparision are either "Higher the better" or "Lower the Better". But if I want to dynamically apply the conditional formatting based on the filter selection, then how should I do it?

 

At present, I am using following DAX for conditional formating:

 
Revenue KPI Color (HTB) = SWITCH(
     TRUE()
     ,SUM(Data[KPI_Value]) > SUM(Target[KPI_Value]) , "#f44242"
     
     ,SUM(Data[KPI_Value]) < SUM(Target[KPI_Value]), "#5ff442",
     "#f44242"
)
Revenue KPI Color (LTB) = SWITCH(
     TRUE()
     ,SUM(Data[KPI_Value]) < SUM(Target[KPI_Value]) , "#f44242"
     
     ,SUM(Data[KPI_Value]) > SUM(Target[KPI_Value]), "#5ff442",
     "#f44242"
)

I have created a KPI table with the list of all available KPIs categories and sub-categories. I have included another column for flag for Lower the better. Using this table, I would like to drive the KPI color selection for Higher the Better or Lower the better.

 

Enclosing the sample pbix file here

 

Any help in this regard will be highly appreciated.

 

Thanks,

Vivek





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

Kudos will be cherry on the top 🙂


Proud to be a Super User!




1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Customized Conditional Formatting (Using DAX)

I had the same issue, forgot to mention you need to transform your column where you define lower is better to number.

Referring to the column on the measures selection.

Regards,
MFelix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

4 REPLIES 4
Highlighted
Super User V
Super User V

Re: Customized Conditional Formatting (Using DAX)

hI @vivran22 ,

 

You can use one of the two measure below to make your condittional formatting:

 

Conditional Low and High =
SWITCH (
    TRUE ();
    SUM ( Data[KPI_Value] ) > SUM ( Target[KPI_Value] )
        && SELECTEDVALUE ( 'KPI'[Lower The Better] ) = 0; "#f44242";
    SUM ( Data[KPI_Value] ) < SUM ( Target[KPI_Value] )
        && SELECTEDVALUE ( 'KPI'[Lower The Better] ) = 0; "#5ff442";
    SUM ( Data[KPI_Value] ) < SUM ( Target[KPI_Value] )
        && SELECTEDVALUE ( 'KPI'[Lower The Better] ) = 1; "#f44242";
    SUM ( Data[KPI_Value] ) > SUM ( Target[KPI_Value] )
        && SELECTEDVALUE ( 'KPI'[Lower The Better] ) = 1; "#5ff442";
    "#f44242"
)
Conditional Low and High = 
IF(SELECTEDVALUE('KPI'[Lower The Better]) = 1 ; [Revenue KPI Color (LTB)]; [Revenue KPI Color (HTB)]
)

Not sure if the order is correct based on your file but the colours change when compared to the visuals you have on your report.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Super User II
Super User II

Re: Customized Conditional Formatting (Using DAX)

@MFelix 

 

Hi,

 

Thank you for your response. I have tried using both the DAX shared but I am not able to select the DAX under Format by - Field Value option (Conditional Formating). The DAX appears to be greyed out or something like that.

 

Please advise.

 

Thanks,

Vivek





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

Kudos will be cherry on the top 🙂


Proud to be a Super User!




Highlighted
Super User V
Super User V

Re: Customized Conditional Formatting (Using DAX)

I had the same issue, forgot to mention you need to transform your column where you define lower is better to number.

Referring to the column on the measures selection.

Regards,
MFelix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Super User II
Super User II

Re: Customized Conditional Formatting (Using DAX)

Thanks! It worked.

Rgds,
Vivek




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

Kudos will be cherry on the top 🙂


Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors