Forum Discussion

aprilysxxx's avatar
aprilysxxx
Regular Visitor
2 years ago
Solved

Conditional formatting the clustered column chart with multiple measures

Hi everyone,   I am trying to conditional format the clustered column chart by the value in each bar:  5% or More = Red 2% to 4% = Yellow 2% or Less = Green   I know I am having multiple...
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    Hi aprilysxxx ,

     

    From your description, please try the following steps:

    My Sample:

    1.Please try code as below to Create a X-axis table.

    X-axis = 
DATATABLE(
    " X axis",STRING,
    "Order",INTEGER,
    {
        {"Average",1},
        {"Clothing",2},
        {"Food",3},
        {"Furniture",4}
    }
)

    2. Use the following code to create a measure-Value_Measure.

    Value_Measure = 
            SWITCH(MAX('X-axis'[ X axis]),
                  "Average",[_Average],
                  "Clothing",[_Clothing],
                  "Food",[_Food],
                  "Furniture",[_Furniture]
                )

    3.Select your visual object and do the following in the Visualizations pane.

    Result is as below.

    Best Regards,

    Yulia Yan

     

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

aprilysxxx's avatar
aprilysxxx
Regular Visitor
2 years ago

Hi 123abc,

 

Thank you for the feedback, and I have tried to create a color-coding measure based on your previous reply. When I tried to drop the color-coding measure into the "Visualization" pane - "Data colors" card, I am not sure if I have that option available on my side, and can you please help me understand if I am interpreting your feedback correctly? 

 

Thanks in advance.  

 

123abc's avatar
123abc
Community Champion
2 years ago

The steps I provided were more tailored to Power BI rather than Excel. In Excel, you might not have the same direct option to assign colors to data points using a separate measure in the "Data colors" card.

For Excel, you can still achieve similar conditional formatting by using the following steps:

  1. Create a Color Measure:

    • Use a DAX measure similar to the one provided earlier:

ColorMeasure =
SWITCH(
TRUE(),
[YourMeasure] >= 0.05, 1,
AND([YourMeasure] >= 0.02, [YourMeasure] < 0.05), 2,
[YourMeasure] <= 0.02, 3
)

 

  1. This time, the measure returns numerical values (1, 2, or 3) instead of color names.

  2. Apply Conditional Formatting in Excel:

    • Select the range of cells corresponding to your clustered column chart data.
    • Go to the "Home" tab, click on "Conditional Formatting," and choose "New Rule."
    • Select "Format cells that contain" and set the format for each of your conditions (e.g., 1 for Red, 2 for Yellow, 3 for Green).
    • Apply the formatting you want (font color, fill color, etc.).

This approach doesn't directly assign colors through a "Data colors" card in the "Visualization" pane but achieves similar results through conditional formatting directly on the data cells.

Remember to adjust the measure and conditions based on your actual data model and requirements. If you're using Power BI, the process may differ slightly, and you can utilize the "Data colors" card more effectively.

  • aprilysxxx's avatar
    aprilysxxx
    Regular Visitor
    2 years ago

    Hi 123abc, 

     

    I shared the screenshot of my working process in the reply above, and it is in Power BI. And can you please help me understand if there is a chance to achieve this in Power BI (instead of in Excel)? 

     

    Thanks for the reply.