Follow these steps to add a line to a bar chart or a column chart in Microsoft Power BI. We explain how to add a single line or multiple lines onto a bar chart and give design tips on how to make your dashboard look great.
Adding a line to a bar chart can be a very useful technique to help visualize results against a historical average or a KPI. In the example below, we have a chart of average sales by category, with a dynamic calculation for the average across all categories as a line added to a bar chart in Power BI.
How to Add a Constant Line to a Bar Chart or Column Chart
First create a bar chart or column chart in your Power BI dashboard or report. Once it’s setup, click on the chart to select it.
Under the visualizations panel of Power BI, there are three unique sections. Add data to your visual, Format Your Visual, and a third section on the far right labeled Add Further Analysis to Your Visual which is notated by an icon of a magnifying glass with a chart inside of it.
Click on Add Further Analysis to Your Visual.
There is a sub-section called Constant Line. The Constant line is how you can add a target or average and overlay it with your bar chart or column chart.
Note: The search function in the visualization panel of Power BI will only search within a single section. If you search for a keyword like line, you will have to first click on the relevant section.
How to Define a Constant Line Value
Under the line settings, click on the “ fx “ button under the Line Section. It will bring you to a new window where you can select a field that has been previously created.
The selections work similar to adding a field as a value to a visualization on the add data to visual tab. It can accept calculated measures with different aggregates such as average and sum.
Create a Measure with Static Value for Target Values
If you don’t want to create a dynamic value for a line on a bar chart, you can create a simple measure that defines what the target value is. In this example, we defined our Target as the number 300. This generates a field with that value, where you can then use it as selection for the Constant Line value that then becomes an overlay on the Power BI Chart
Modifying Line Overlay Formatting on Bar Charts in Power BI
Constant Lines have a number of formatting options. You can change the color, transparency and color of the lines along with whether they are solid dashed or dotted. Options are available underneath the button where a constant line has been added to the bar chart visual.
Note: It is possible to have multiple constant lines on a single Power BI chart. You have to select the constant line that you are editing the formatting of to update its specific properties. Toggle between the different lines by selecting them at the top of the Add Further Analysis to Your Visual tab.
Power BI Design Considerations
When adding lines to bar charts in Power BI, there are a few design considerations to keep in mind.
- Turn off background grid lines on the bar chart to avoid confusion. Gridlines are small mostly transparent lines that can overlap and cause confusion while looking at your bar chart.
- Consider using data labels for columns and disable axis titles for a cleaner look.
- Improve visibility of data labels by enabling backgrounds
- Adjust data label position to be above or below the line. Whether the line is above or behind the bar chart is also adjustable and can improve readability.
Before Optimizing Power BI Visualization Design
After Power BI Design Optimizations
Conclusion
Adding a line to a bar chart in Power BI can be a powerful way to show how results compare to a baseline or target. By adding a line to a chart you can present groups of data that would otherwise look much more cluttered than having a number of different KPI Visuals or Card Visuals on a reporting dashboard.