Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ferbyrez
Helper II
Helper II

Conditional formatting based on a dynamic value (slicer)

Hi guys,

 

so, basically every time I change my slicer, a new value is calculated (measure - it´s an avarage)

 

how do I make a conditional formatting based on this value that changes when I click in a differente slicer?

The ideia is to highlight the values above average for each region, but each region has a different avarage.

 

Hope I explained well :

PBIQUESTION.jpg

13 REPLIES 13
ferbyrez
Helper II
Helper II

@Mariusz  many thanks for the PBI File !! So, I tried to do the same but didn´t have the same result in my PBI file.. when I put the same measure you put, the result in the column was not 1 value.. there was a lot of differente values..

SS below. the SLICER is affecting the measure, even with the ALLSELECTED() inside the calcute. 

 

@parry2k many thanks for the advice, I put my measure below.

 

guys, thanks for helping, hope I´m not disturbing you.

 

3333.png

ferbyrez
Helper II
Helper II

@parry2k  @Mariusz  many thanks for helping.

 

@parry2k I created a new measure: If ( [ COLUMN1 ] > [AVERAGE MEASURE] ; "Red"; "Green" )

 

but he is formatting all the cells green..

 

@Mariusz  your solution didn´t work out. PBI doesn´t allow me select your measure.

Mariusz
Community Champion
Community Champion

Hi @ferbyrez 

 

Sorry, you need to set my example like below.

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Mariusz
Community Champion
Community Champion

Hi @ferbyrez 

You can create a measure like below.

Measure 
INT( [COLUMN1] > [AVRAGE] ) 

And use this measure for conditonal formating in your table

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

EDIT:

@Mariusz  i did it, but its not formatting. nothing happens.

 

measures.png

@ferbyrez sorry couple of things are getting mixed up based on solution @Mariusz  and I are providing.

 

If you want to compare value in measures to set the color, you should create another measure like I mentione previously but if you are comparing your measure against fixed value then you should choose the method @Mariusz provided.

 

As you can see there are many option for conditional formatting, you have to see what best fit for you. in case you are comparing two measures I would do something like this

 

KPI Color= 
SWITCH ( TRUE()
[Measure 1] > [Measure 2], "Green",
[Measure 1] = [Measure 2], "Yellow",
"Red"
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

guys, I really appreciate your effots on helping me.

 

I don´t know why it didn[´t work out I tried everything.

 

For some reason PBI is considering all values smaller than average.

 

Sem título.png

 

@ferbyrez it is always good to see what the measure value you are getting at each row. so in the table you showed, drop those two measures whcih you are using in KPI color so that we can see what value you are getting and make sure you are getting expected value otherwise we are shooting the dark, doesn't know what those measures are and how you data models looks like? Read this post to get your answer quickly and it saves everyone time.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Mariusz
Community Champion
Community Champion

Hi @ferbyrez 

 

Please see the pbix file with an example

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Mariusz
Community Champion
Community Champion

Hi @ferbyrez 


Please see the link below with example pbix file. 

https://drive.google.com/file/d/1XjQb4VwBUwatVFbLIW-GKiQl3ZGOTT5z/view?usp=sharing 

 

Hope this helps!

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Mariusz
Community Champion
Community Champion

Hi @ferbyrez 

Have you changed to "Format By" = Rules?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


 

@ferbyrez and make sure in conditional formatting, you choose Format by Field Value and then select KPI Color in drop down list

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@ferbyrez you can create another measure for KPI Color based on your mesure

 

for example

 

KPI Color = IF( [Measure] > 1, "Red", "Green" )

and now you can use this KPI Color  measure on conditional formatting and color will change based on your original measure.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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