Power BI is rich with features for data visualization, one of which is the capability for dynamic shading in Power BI native line charts. In this guide, we’ll explore how to compare current year sales to previous year sales by shading the area between the two lines in a line chart using the error bars and bands feature.
Introduced in 2022, error bars are a useful yet sometimes overlooked feature in Power BI. It not only gives you the option to add traditional error bars and bands in Power BI. It also allows for dynamically shading areas in your line chart based on measures. In this example I will show you how to compare current year sales vs previous year sales by shading the area between the two lines in either green or red.
Show me the shades!
Follow the steps below.
- Create a native line chart and add the measures and axis of choice. In the provided example I have Sales and Sales PY (Prior Year) by year and month.
The sales measure is just a sum of sales amount from a fact table. The Sales PY is a measure calculating the same values for the previous year.
It should look something like this.
- Create a new measure that returns the lowest value from both Sales and Sales PY
Sales lowest value = MIN([Sales],[Sales PY])
- Enable error bars by going to visual analytics pane -> Error bars. Select the first series and enable under options.
Set Type to By field and add measure Sales lowest value to Upper bound.
- Enable Error band and disable Bar. Add a band color. This color will fill the area whenever Sales is above Sales PY. I’ve chosen a light green color #A5FFAF.
- Switch over the Error bars series to Sales PY. This time, add the same measure Sales lowest value to Lower bound. Make sure to enable the error bars for this series.
- Enable Error band and disable Bar for series Sales PY. Add a band color. This color will fill the area whenever Sales is below Sales PY. I’ve chosen a light red color #FF98A0.
- Your filled line chart is ready!
In the example shown the lines are set to Line type = smooth. This causes a slight mismatch between the filled area and the rounded corners of the line series.
By following the outlined steps, you’ve now created a visually intuitive line chart to compare sales figures between two time periods using dynamic shading in Power BI native line charts. This feature of dynamic shading not only aids in quickly identifying the variances but also in better understanding the data at a glance. Experiment with different settings in Power BI to optimize your visualizations further, and don’t hesitate to apply this feature when analyzing other metrics or time periods as well.
Something to be aware of when using error bands this way, is that whenever the two (or more) series crosses each other, the shaded areas could be colored a bit strange. This is because the upper / lower bound intersects the series line at a different point than the actual series value.
Take a look at the image below. The red area between point A and C should probably not be colored red. It should probably not be colored at all in this case.
The reason for this behavior is because there is a line drawn between point A and point B which crosses the Sales series at a point which is between the two different data points.
This will probably not cause issues with how areas on each point is colored, but more how the area between different data points is colored.
Thank you for reading
If you enjoyed this guide or learned something from it, please share it with others or comment below.
What is your experience using Error bands in Power BI?
Do you have any tips or want to share your own creative visuals? Please share them below!