Power BI is an incredibly powerful business intelligence service with tons of capabilities that grow by the week. A very common task you want to do is track a metric over time, and usually this is against a goal. For example you are tracking employee sales or utilization which is nice, but for the employee we want to make it easy for them to see where the goal is. In Excel this might have involved adding another axis or other method, but using Power BI I want to show you a built-in way in which you can add some snazzy Goal lines to your line or bar charts!
Business Case
There are all sorts of reasons for this, but perhaps you are tracking employee time, and you want to make sure they are entering 8 hours of time a day. Or perhaps you are tracking employee sales, or if you’re a consultant or service provider you are tracking how utilized your technicians are per month. And like most businesses all of those metrics have a desired goal and ideally we would like to see that on our charts.
Getting to Know Constants in Power BI
In our company Contoso Electronics let’s say that we have this simple bar chart here where are tracking employee hours by week.
Employees should be putting in 40 hours a week right? At a glance it LOOKS like that has been the case, but how can we be sure? We want to see a goal line of 40 hours a week so we spot weeks where employees might have missed some time. To do this, we are going to use a Constant Line in the Analytics tab of your chart properties pane. Let’s see what we’re going to achieve:
- To do this, in Power BI Desktop, highlight your line or bar chart, and click the Analytics button in the Visualizations pane
- Click to expand Constant Line, and click Add. Give it a name like Goal, and set your properties as desired. In our case, we type in the value number 40 for 40 hours a week. Set the color as desired that will stand out.
- I suggest clicking the option to show a Data label, and for the text choosing Name and Value. Set the same color as the goal line.
This is what my Constant Line configuration looks like:
Done! Wow that easy. Let’s do this for a line chart. In this chart, we are tracking employee utilization. For our service providers, let’s say that should be ideally 80% billable. Configure a Constant Line with a value of 0.8, and voila:
I just typed in values, but they can be dynamic as well. There are way more options than I can discuss here, so I encourage you to review the documentation on the Analytics page. Depending on the columns or measures and chart type, you can use dynamic min/max, average, median and percentile lines.
If you’re interested in learning more about Power BI and how it can help to better UNDERSTAND your business, contact us!
