Color coding based on changing baseline and target

[sdegroot]

Hello, 

I'm trying to create a line and cluster column chart that changes colour when the baseline and the target are met. One colour below the baseline, one colour above the baseline and one colour above target.  Each field that is chosen corresponds with a different baseline and target. It looks like color saturation is not available in this type of visualization and even if it was, it looks like I have to choose a specific value that reflects the baseline and target but each field's target and baseline is different. I'm thinking this is not possible...?

Here's what I have so far. Idealy Q1 should be red, Q2 and Q4 should be yellow, and, pretending Q3 reached the target, it would be green.

 

Thank you!

[v-qiuyu-msft]

Hi @sdegroot,

You can try to create a calculated column to return result whether the actual value meet the baseline or target. Then use this new column in the Column Series property.

ColorCondition = SWITCH(TRUE(),'Table2'[Actual]<'Table2'[Baseline],"Red",'Table2'[Actual]>='Table2'[Baseline] && 'Table2'[Actual]<'Table2'[Target],"Yellow",'Table2'[Actual]>='Table2'[Target],"Green")

Best Regards,
QQiuyunYu

[sdegroot]

Thank you, that was very useful!

It looks like I cannot use this same method when creating a table. The table should follow the same code as below but, from my understanding of previous posts, conditional formating is not available from a value in a table unless it is an aggregated value. Is this true?

[prathy]

Hi,

Kind of conditional formatting you are after; I don't think it is available out of the box. But, if you want you could probably add some custom columns and add overlays of charts to get something like below. NB: Works better only when you don’t want visuals to interact with other visuals on the report I don’t think, my idea is a best practice but a work around 🙂

If you wnat I will share my PBIX file, let me know!

HTH!

Prathy

[sdegroot]

Hello Prathy,

Thanks for you response. I think we're looking for something a little more asthetically appealing so I've used the option of just using a column chart and using Diverging Data Colors. Unfortunately, because I have to state a minimmum and a maximum # it does not work well with the different fields as each field has a different baseline and target :(. Basically, here's what's happening now.

I properly set up one field indicator:

But the next one will look like this since the the color coding is not based on a field: