100% stacked column chart and constant line

[WJ876400]

Hi,

I have the below 100% stacked column chart that has a yearly period as a small multiple, the constant line has added to each yearly period. However I just want the constant line on the last year 22/23, is that possible?

thank you for any help

[123abc · Accepted solution]

In Power BI, if you want to add a constant line to a 100% stacked column chart but only have it appear in the last year (22/23 in your case), you can achieve this by using a combination of measures and conditional formatting. Here's how you can do it step by step:

1. Create a Measure for the Constant Line Value:

   • Go to the "Model" view in Power BI.
   • Create a new measure that calculates the constant line value. For example, you can use a SWITCH statement to return the constant value only for the last year, and zero for other years. Here's an example DAX formula:

ConstantLineValue =
SWITCH(
MAX(YourYearColumn),
2022, 100, -- Change these values to match your year (e.g., 22/23)
2023, 100, -- Change these values to match your year (e.g., 22/23)
0 -- Default value for other years
)

1. Make sure to adjust the years and values to match your data.

2. Add the Constant Line to the 100% Stacked Column Chart:

   • Go to your report page.
   • Select the 100% stacked column chart.
   • In the "Visualizations" pane, go to the "Format" section.
   • Expand the "Data colors" section and enable the "Constant line" option.

3. Configure the Constant Line:

   • After enabling the constant line, you should see a new section in the "Visualizations" pane for configuring the constant line.
   • Set the "Constant value" to use the measure you created earlier (e.g., ConstantLineValue).

4. Conditional Formatting:

   • Now, you need to use conditional formatting to control when the constant line should be displayed.
   • Select the constant line section in the "Visualizations" pane.
   • Under "Conditional formatting," select "Color scale."
   • Set up the color scale to make the constant line invisible for all years except the last year (e.g., 22/23). You can use your measure (e.g., ConstantLineValue) as the field for conditional formatting.

   You can configure the color scale so that the constant line is only visible when the measure equals the value you specified for the last year (e.g., 100).

This way, the constant line will only appear in the last year (22/23) on your 100% stacked column chart. For other years, it will be hidden due to the conditional formatting. Adjust the measure and conditional formatting settings as needed to match your specific data and chart configuration.

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.