Conditional formatting the clustered column chart with multiple measures

[aprilysxxx]

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 measures in the chart, and is it possible to achieve this conditional formatting?

Or alternatively, is there a DAX formula that may be able to help with this? 

Many thanks! 

[v-weiyan1-msft · Accepted solution]

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.

[123abc]

Yes, you can achieve conditional formatting in a clustered column chart with multiple measures using DAX expressions in Power BI. Here's a general approach you can follow:

1. Create a Calculated Column: Start by creating a calculated column that represents the conditions you want to format. In this case, you want to categorize the values into three groups - Red, Yellow, and Green.

   DAXCopy code

   ColorCategory = SWITCH( TRUE(), YourMeasure >= 0.05, "Red", YourMeasure >= 0.02 && YourMeasure < 0.05, "Yellow", YourMeasure < 0.02, "Green" )

   Replace YourMeasure with the actual name of the measure you want to use for conditional formatting.

2. Apply Conditional Formatting: Once you have the calculated column, use it for conditional formatting in your clustered column chart.

   • Click on the column chart visual in your report.
   • Go to the "Visualizations" pane.
   • Find the "Data colors" card.
   • Drag and drop your ColorCategory calculated column onto the "Data colors" field.

3. Adjust Colors: After you've applied the conditional formatting, you can adjust the colors for each category.

   • Click on the paint roller icon in the "Visualizations" pane.
   • Under "Data colors," you can manually set the colors for each category (Red, Yellow, Green).

Remember to replace YourMeasure with the actual measure you are using in your chart.

This approach assumes that you want to color the entire bar based on the conditions. If you want to color specific portions of the bar differently, you might need to explore more advanced techniques using custom visuals or a combination of measures.

Always make sure to adjust the expressions based on your specific data model and requirements.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[aprilysxxx]

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]

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]

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.