Here’s a question from Joey :
I’m creating performance reports in Power BI, and I need to add a target line to my charts. How can I add a reference line to scatter, bar, and line charts to show our company’s performance goals across different metrics?
Our Answer
This tutorial will guide you through the process of adding reference lines to scatter, bar, and line charts in Power BI, using an HR performance scenario as an example.
Data Model
Assume we have a single table named “Employees” with the following columns:
- Employee ID (Text)
- Department (Text)
- Month (Date)
- Sales Amount (Decimal)
- Customer Satisfaction (Decimal)
- Productivity Score (Decimal)
Step-by-Step Instructions
1. Create a measure for the reference line in Power BI
First, we will go ahead and create a measure for our target line. In this example, let’s set a monthly sales target of $10,000.
In Power BI – from the Modeling tab create the following measure definition:
Sales Target = 50000
2. Add a reference line to a bar chart
- Create a stacked column chart using “Month” on the axis and “SalesAmount” as the value.
- Select your chart, then from your right side pane, hit Format.
- In the Format section, scroll down to the Reference Line section.
- Define your reference line settings and customize the line’s appearance (color, style, etc.) as needed. Note that we use the measure defined in the previous step to define the value of the benchmark line.
Here’s our custom line definition:
Here’s our chart with the benchmark line:
3. Add a reference line to a line chart
- Create a line chart using “Month” on the axis and “ProductivityScore” as the value.
- Create a new measure for the productivity target:
Productivity Target = 85
- Follow steps in the previous section (bar chart instructions), using the “Productivity Target” measure.
4. Add a reference line to a scatter chart
- Create a scatter chart with “SalesAmount” on the X-axis and “CustomerSatisfaction” on the Y-axis.
- To add a vertical reference line, use the “Analytics” pane and add a constant line for the X-axis.
- To add a horizontal reference line, repeat the process for the Y-axis.
Remember
- If the reference line doesn’t appear, check if the measure is correctly defined and selected.
- Ensure the line’s value falls within the chart’s axis range. Adjust the axis settings if necessary.
- Combining multiple reference lines (e.g., in scatter plots) creates quadrants for easy categorization.