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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Gauge conditional formatting based on other measure

Hello Community,

 

Perhaps one of you know a solution for the following.

I have a gauge that shows a target gross margin that a team needs to hit. What I would ideally want to show is the actual margin as well as the forecasted margin. Because you can only have 1 value in a gauge, I thought maybe it's possible to have the forecasted margin as value while using conditional formatting to fill the gauge based on actual margin. Can this be done? I only found options to format based on fixed values rather than changing values/a measure.

 

Thanks in advance.

13 REPLIES 13
Anonymous
Not applicable

Thanks for the response. Is it possible to use a similar measure to get 2 colours in 1 gauge though?

For example, I can have an actual margin of 300k, a forecasted margin of 900k and a target of 700k.

I would like to have the forecasted margin up to 300k show up as yellow and the rest of the value up to 900k as light yellow. 

Hi, @Anonymous 

 

Of course, you need to write an if function to assign two cases according to your needs, and then use conditional formatting.

If you can share some sample data and your desired result, I will help you in detail.

 

Best Regards,
Community Support Team _ Janey

Anonymous
Not applicable

Below is the result I would like to achieve so it's easy to see the forecasted margin as well as the current realised margin. I did create a measure with "BonusColour = IF(MeasureTable[Bruto Marge Totaal]<[ForecastedMarge],"#FFE000","#FFF399")" but I don't think that's how it works 😄

 

PJVisscher_0-1650361131238.png

 

@Anonymous 

 

How is your conditional formatting set up?

Anonymous
Not applicable

Within Visual > Colors I selected Fill format style "Field Value" with the field it's based on being the measure "BonusColour".

Given the measure though, I think it would end up being either one or the other colour rather than having both in the same gauge as in the example photo which is more like the gradient style but rather than using fixed values it's based on measures and rather than gradient it's a hard cut-off of colour

Hi, @Anonymous 

 

I checked it, the content on your screenshot doesn't look like a gradient, just an extra piece of white. So I think you should check if that white section corresponds to the data of [Bruto Marge Totaal] or [ForecastedMarge].

vjaneygmsft_0-1650422661383.png

If it is convenient, can you share a sample file?

Anonymous
Not applicable

Ah that's because I have a measure that makes the maximum value of the gauge 10% higher than either the target or forecasted margin. Where I would llike the 2 colours is in the yellow bar like so: 

PJVisscher_0-1650439489465.png

The visual would end up as:

Gauge target = stand-alone target measure

Gauge max = stand-alone measure that adds 10% to higher value of forecast/target

Value = forecasted Margin

Colour of value = color 1 for up to actual margin, color 2 for rest

 

Unfortunately I'm not sure how to make an easy sample file for this.

Hi, @Anonymous 

 

Have you tried to fill the vacancies? I mean force the blank part to be the rest color.

Like this:

BonusColour =
IF (
    MeasureTable[Bruto Marge Totaal] = MAXX ( ALL ( table ), [Bruto Marge Totaal] )
        || Table[ForecastedMarge] = MAXX ( ALL ( table ), [ForecastedMarge] ),
    "#FFF399",
    IF (
        MeasureTable[Bruto Marge Totaal] < [ForecastedMarge],
        "#FFE000",
        "#FFF399"
    )
)

 Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks for the continued help.

I replaced the measure with yours and gave the full value part of the gauge the alternate color (light yellow) rather than just the part where Forecast > Actual. It does leave the intended 10% white part

 

(Forecast will basically always be higher than actual, mostly because forecast is 6 months and actual 1-6)

@Anonymous  Still not working?

Anonymous
Not applicable

It turns out like this:

PJVisscher_0-1650529879729.png

Rather than this:

PJVisscher_1-1650529934334.png

 

Hi, @Anonymous 

 

I need some sample data and all your measures used in this visual.

v-janeyg-msft
Community Support
Community Support

Hi, @Anonymous 

 

Conditional formatting can also be modified by custom measures or columns.

You can refer the thread:

Solved: Conditional Format Gauge Charts - Microsoft Power BI Community

 

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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