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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DeepshikhaNorsk
New Member

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

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 

2 ACCEPTED SOLUTIONS
VijayP
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


View solution in original post

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.

vjtianmsft_0-1723015669273.png


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"
    )
)

 

vjtianmsft_1-1723015800165.pngvjtianmsft_2-1723015806783.png

vjtianmsft_3-1723015814781.png

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.

 

View solution in original post

4 REPLIES 4
VijayP
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


DeepshikhaNorsk
New Member

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

DeepshikhaNorsk_1-1722865166128.png

 

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.

vjtianmsft_0-1723015669273.png


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"
    )
)

 

vjtianmsft_1-1723015800165.pngvjtianmsft_2-1723015806783.png

vjtianmsft_3-1723015814781.png

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
Super User
Super User

@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)




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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