Building a visualization that shows performance to goal is a common practice in BI. Often the “Line and Clustered Column Chart” visualization is used. The line typically shows the goal and the bars show the actual metric for the given period. An excellent way to draw quick attention to the periods that have achieved the goal is to dynamically change the color of the bar when the goal is met or exceeded.
Below is an example of a visualization that shows monthly Sales Amount (bars) and the Sales Goal (dashed line). Each time the value of the Sales Amount exceeds the value of the Sales Goal, the color is set to green. When the Sales Amount falls short of the Sales Goal, the bar color is set to tan.
The key to building this visual is to create a Delta to Goal value. This can be done by adding a calculated column or creating a measure. In this example the Delta to Goal field was already added.
Power BI makes this very easy to achieve the desired visualization by simply applying these simple steps.
Add a Line chart & clustered column chart to the canvas.
Place the Date in the Shared axis field, the Sales Amount in the Column values field, and the Sales Goal in the Line value field.
With the visualization still selected, click on the Format icon (paint roller) in the visualization pane.
Open the Data colors section of the Visualization pane and set the Sales Goal color to Gray and the Sales Amount color to Green. NOTE: it is important to set the Sales Goal to the color that you want to use to show “meeting or exceeding the goal”. If you do not set the color before applying conditional formatting, the default color (usually blue) will appear next to the Sales Amount Label in the Legend.
After setting the Sales Amount color to green, click on the fx button under the Default color selection. This will open the Default Color – Data colors screen.
Change the Based on field to Sum of Delta to Goal. This value is used to determine if the Sales Amount is greater than the Sales Goal. The color of the bar will be determined by whether the delta value is greater than or less than 0.
Select Custom from the Minimum drop down, enter 0 in the field below Custom, and choose the color Tan from the color drop down (in this example, a custom color D9CBA0 was used for tan).
Do the same for the Maximum drop down, select Custom, enter 0 and choose the color Green. Setting the minimum and maximum values to 0 will cause any value below 0 to display the minimum color and any value greater than or equal to 0 to display the maximum color.
Click OK to save.
I hope this simple example will help your performance-to-goal visualizations really stand out. Contact a Power BI specialist today with any questions.
Written by Jim Hill – Senior BI & Analytics Consultant at JourneyTEAM