Conditional Formatting on Line Y axis value in Line & clustered column visual chart

[DeepshikhaNorsk]

I have three columns on Y axis as Revenue, budget and last year Revenue. X axis have Month Jan to Dec. 

Added line-Y axis as "Budget difference last year" with conditional formatting on value stating greater than 0 should be black otherwise Red.

It did not work as is when there is drill down based on pie chart 

[VijayP · Accepted solution]

@DeepshikhaNorsk  this is because , drill down changes the value to lower value than the overall value.

Set the condition accordingly based on drill down values

[VijayP · Accepted solution]

@DeepshikhaNorsk  this is because , drill down changes the value to lower value than the overall value.

Set the condition accordingly based on drill down values

[DeepshikhaNorsk]

I have tried this option to add custom label which worked perfectly for me. Later on drill down its not working as is.

[v-jtian-msft · Accepted solution]

Hi,VijayP,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@DeepshikhaNorsk .I am glad to help you.
In fact,the suggestion made by VijayP  is very good, but the conditional formatting of the fields in the system you are using does not seem to support the drilling down effect, I think you just need to create MEASURES on top of what you have now to achieve your drilling down requirements
As you can see, you can customize the conditional format based on the value of the measure when the data is filtered by drilling down to different fields.

Example:
- When drilling down to the smallest unit (day), I judge greater than 100 to show blue;
- When drilling down to months, I judge greater than 500 to show blue;
- When drilling down to Quarterly, I judge the blue color to be greater than 1000;
- When the current x-axis filter range of the line/bar chart is the largest year, I judge the total value to be greater than 10000 to show blue.
By defining conditions that dynamically modify the determination according to the current visual X-axis filtering units, combined with the use of custom condition formats, you can well meet your needs: in the case of drilling down, different X-axis levels use different condition formats (rather than using the condition format of a particular field, which is specific to the entire dataset, and doesn't support dynamic drilling down effects).
All you need to do is write a measure that meets your needs, and rely on the ISINSCOPE() function in conjunction with the SWITCH function to reverse the units of the X-axis to achieve your needs.
Since the SWITCH function is executed from top to bottom, once the conditions above are met, it will not execute the judgment below. So you need to drill down to the bottom of the hierarchy written at the beginning of the judgment, the largest unit in the last judgment (measure in the reverse hierarchy, because the month contains the day, the quarter contains the month, the year contains the quarter, according to the relationship between the transfer: the year contains the day).

You can create the following measure(the ones I have provided are only examples, you will need to write measures to meet your own data based on your real data)

M_color2 = 
VAR _diffValue = SUM('Table'[Budget Difference Last Year])
RETURN  
    SWITCH(TRUE(),
    ISINSCOPE('Table'[Date].[Day]),
    SWITCH(TRUE(),
    
        _diffValue >= 0 && _diffValue < 300, "black",
        _diffValue < 0, "red"
    ),
    ISINSCOPE('Table'[Date].[Month]),
    SWITCH(TRUE(),
        _diffValue >= 0 && _diffValue < 500, "black",
        _diffValue < 0, "red"
    ),
    ISINSCOPE('Table'[Date].[Quarter]),
    SWITCH(TRUE(),
        _diffValue >= 0 && _diffValue < 1000, "black",
        _diffValue < 0, "red"
    ),
    ISINSCOPE('Table'[Date].[Year]),
    SWITCH(TRUE(),
        _diffValue >= 0 && _diffValue < 10000, "black",
        _diffValue < 0, "red"
    )
)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[VijayP]

@DeepshikhaNorsk 

In that chart for line there is no conditional formatting option, But you can use Custom Label ( Serach for Details) and add a custom condition with a value it should work (Label will show the color)