Creating a Linear Gauge with Multiple Targets in Power BI

Have you ever wondered if it’s possible to have a linear gauge with not just one, but two target lines?? Recently while discussing with someone this question came up and I found the idea intriguing and started visualizing how this feature could enhance data visualization. To my surprise when I researched on this feature, I barely found any content addressing this topic. So, here I am, sharing some of my insights on creating a linear gauge with multiple targets.

In this article, we’ll explore three important questions about the gauge with multiple targets:

1. Why It’s Helpful
2. When Do We Need It?
3. How to Create One in Power BI Using Bar Chart?

Let's start with the topic.

Why It’s Helpful

Here are the reasons why Actual and Target comparisons are helpful and the role of multiple targets.

• An Actual and Target comparison helps track deviation in the performance.
• When you want to compare your actual value against a forecasted target.
• An Actual and Target comparison is helpful when we want to track crucial KPIs regularly.
• An Actual and Target comparison is helpful when we want to read multiple categories against a metric on the same scale at a glance.

• A gauge and multiple target line visuals are flexible to use across a wide range of business requirements.

When Do We Need It?

The previous section talked about why a Gauge is helpful. Let's dig in more and find out the specific cases where 2 targets are helpful.

• When we want to display a goal, the current data set and previous datasets are all in one visualization.
• Multiple target lines can be helpful when you want to display primary and secondary targets. E.g. You want to compare actual sales performance with last year's sales as well as the projected sales for this year. When comparing values against average, median, or mode in statistics.
• When your multiple targets are independent of each other. Grouped targets, on the other hand, are treated as a single unit. If a group has multiple targets they get separated as bands: bad-neutral-good

• Multiple target lines can help understand performance against various benchmarks. E.g. You want to rate a movie against IBDM and Rotten Tomatoes ratings for the given genre.

How to Create One in Power BI Bar Chart?

I didn't find any radial or linear gauge that offers this feature. So I decided to re-create something similar using the Power BI Bar chart. Follow these step-by-step guides.

Step-by-Step Guide

1. Create the Measures: Use DAX formulas to calculate the measures mentioned above. For example:

Total Sale = SUM(Orders[Sales]) 

Total Cost = [Total Sale] - [Total Profit] 

Sale LM = CALCULATE([Total Sale], DATEADD('Order Dates'[Date], -1, MONTH)) 

Remaining = [MaxPoint] - SUM(Orders[Sales]) 

MaxPoint = MAXX(VALUES('Order Dates'[Month Name]), MAX([Total Sale], [Sale LM])) * 1.2        

2. Create a Stacked Bar Chart:

• Use “Month Name” as the Y-axis and plot Total Sale and Remaining on the X-axis.
• Color the remaining bar in light grey and the total sale bar in a primary color (e.g., yellow, blue).

3. Add Reference Lines for Targets:

• Insert constant reference lines for Total Cost and Sale LM. These lines represent your dual targets.

4. Format and Style: Customize the chart’s appearance:

• Add descriptive legends.
• Apply border roundness and shadows for a modern look.

5. Enhance Tooltip Readability: Create a tooltip measure (e.g., PY Tooltip) to display relevant information:

• Show the Total Cost and the percentage difference from the Total Sale.
• Use arrows (? or ?) to indicate positive or negative trends.

PY Tooltip= 
	VAR __T1 = [Total Cost] 
	VAR __T2 = [Total Sale] 
	VAR __T3 = __T2 - __T1
	VAR __P1 = DIVIDE(__T3,__T2)
	VAR __P1Color =
	    IF(__P1 > 0,
	        " ?", // up arrow
	        " ?" // down arrow
	    )
	VAR __R1 = FORMAT(__T1, "$0,K") & " | " & FORMAT(__P1, "+0%") & __P1Color
	RETURN __R1	
	
Target Tooltip = 
	VAR __T2 = [Total Sale] 
	VAR __P2 = [Variance Sale LM %]
	VAR __P2Color =
	    IF(__P2 > 0,
	        " ?", // up arrow
	        " ?" // down arrow
	    )
	VAR __V2 =
	    IF(__P2 > 0,
	        FORMAT([Variance Sale LM %], "+0%"),
	        FORMAT([Variance Sale LM %], "0%")
	    )
	VAR __R2 = FORMAT([Sale LM], "$0,K") & " | " & __V2 & __P2Color
	RETURN __R2        

6. Review and Refine: Compare your result with the expected outcome. Adjust styling as needed.

By following these steps, you’ll have a linear gauge with two target lines, providing richer insights into your data.

I hope these DAX and information are helpful.