Highlight value above constant line in Clustered Column Chart

[nidhii_019]

Help Guys! 

This is my clustered Column chart and I added a Constant line called target (1%). I want help in making the values above the target line to be red and the values below 1% to be in blue. I am not able to add conditional formatting to this chart if we have two variables on the Y axis. Is there any other way I can differentiate the values above and below target value.

I tried conditional formatting the column color value and it does not show me the fx option there 

                

Any suggestion would help please. Thank you in advance

[nidhii_019]

This is really smart. Thank you so much for the solution Leroy, will definetly give this a try!!!

[Anonymous]

Hi,@nidhii_019 
It is a pleasure to receive your compliments and hope my advice is useful.

If you wish, consider accepting your solution as a solution that will also benefit other community members who have the same problem as you and find a solution faster.

Of course, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu

[Anonymous · Accepted solution]

Hi,@nidhii_019 
Based on what we've learned so far, your needs may not be available in a single visualization for the time being. However, we can consider using an overlapping visualization implementation.

My solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

Measure = IF(SUM('Table'[VALUE])>=25,25,SUM('Table'[VALUE]))

Measure 2 = IF(SUM('Table'[vlaue2])>25,25,SUM('Table'[vlaue2]))

3.Create a histogram as the underlying histogram: 

4.Create another one as the top-level histogram:

5.Then modify his transparency to overlap the two visualizations.

6.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

 
Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.