Power BI Chart Tips : Dumbbell Chart and Range Highlighting
"A common limitation in standard Power BI reporting is the flat representation of data, which often fails to capture the nuanced relationship between historical performance, current results, and strategic targets. From a data architecture perspective, 'high-value' visualization is about minimizing cognitive load while maximizing the density of insights—a critical factor that determines user engagement and decision-making speed.
In this guide, we move beyond simple line charts to implement a Dumbbell Chart using the Error Bars technique. By strategically layering 'Growth' and 'Decline' as separate measures to form a structured data hierarchy, we clearly illustrate the variance between TY (This Year) and LY (Last Year) while intuitively highlighting key KPIs.
In the following sections, we will explore the specific methodology for creating a sophisticated Dumbbell Chart using Error Bars—a technique that, while challenging to achieve with default features alone, provides the visual depth shown in the image below."
1. Sample Data
2. Implementing the Line Chart
Rather than simply connecting two points, we express 'Year-over-Year Growth' with a thick line and the 'Current Target' with a slender marker.
Step 1: Basic Visual Object Setup
- Visualization → Select Line Chart.
- X-axis → Add Category.
- Y-axis → Add the following three fields: TY Actual, LY Actual, Target
Step 2. Removing Lines (Core Step)
To create the dumbbell shape, we must remove the connecting lines.
- Format → Lines
- All Series → Stroke width = 0
Step 3. Marker Settings
Leave only the dots (●) to represent the status.
- TY → Blue circle (Size 8)
- LY → Gray circle (Size 8)
- Target → Orange (Size 6, Diamond)
3. Implementing the Error Bar Dumbbell via Measures Split
Since the Error Bar feature does not support native conditional formatting, I have created separate measures for 'Growth' and 'Decline' so that the bars appear selectively depending on the data status.
Basic Concept
- Upper → Top value
- Lower → Bottom value
- Connecting these two generates a line.
1) Visual Strategy
Instead of a simple listing, separate the series based on the 'nature' of the data and stack the layers.
| Case | Series to Apply Error Bar | Upper | Lower | Business Meaning |
|---|---|---|---|---|
| Growth Section | TY Series Error Bar | TY | TY Series_Lower | Normal growth point |
| Decline Section | LY Series Error Bar | LY | LY Series_Lower | Negative growth/Crisis point |
2) Core DAX Logic for Implementation
This trick involves returning BLANK() when conditions are not met, effectively removing that specific bar from the chart.
① For Growth Section (Used as the Lower bound for the TY series)
② For Decline Section (Used as the Lower bound for the LY series)
3) Detailed Error Bar Settings
You can configure the options by inserting the corresponding measures as shown in the image. In the Analysis pane, repeat the following settings twice while switching the Series. The key is accurately matching the 'Lower bound measure' for each scenario.
Common Settings: Error bars: On / Width: 10px (To create the dumbbell body) / Cap: None
[Configuration Table]
| Setting Item | Growth Section (Normal) | Decline Section (Highlight) |
|---|---|---|
| Series Application | Select TY Actual | Select LY Actual |
| Upper Bound | [TY] (Default) | [LY] (Default) |
| Lower Bound | [TY Series_Lower] | [LY Series_Lower] |
| Bar Color | Light Gray (#D1D1D1) | Red (#FF4B4B) |
| Bar Width | 10 px | 10 px |
| Cap Shape | None | None |
4. Target Line Setup
The Target is represented as a reference point rather than a line.
- Color: Orange
- Line: Dashed (Optional)
5. Adding Achievement % Labels and Label Organization
Create Achievement % as a separate measure and display it as a data label.
1) Removing Unnecessary Labels (Focus)
- Path: [Format] pane > [Data labels]
- Setting: First, set the [Apply to all] option to 'Off'.
- From the series selection dropdown at the top, select only [TY Actual] and switch it to 'On'.
- Keep the remaining LY and Target series in the 'Off' state.
2) Changing TY Labels to Achievement %
- Create Measure (DAX): Achievement % = DIVIDE( SUM('SalesData'[TY Actual (TY)]), SUM('SalesData'[Target (Budget)]), 0)
- Connection Method:
- Navigate to the [Data labels] > [Values] section.
- Field: Remove the default TY Actual and insert the newly created [Achievement %].
- Color: Select orange or a high-contrast color for emphasis.
3) Adding Actual Sales as Supplemental Information (Detail)
- Path: [Data labels] > [Detail] section, toggle to 'On'.
- Data: Drag and drop the original [TY Actual (TY)] field here.
- Design: Adjust the font size to be 1–2pt smaller than the main label (Achievement %) and set the color to a calm gray tone.
4) Layout Configuration: Multi-line
- Path: [Data labels] > [Options] > [Layout] → Select 'Multi-line'.
- Result: Information such as "105% (1,480)" will be displayed dimensionally above the TY endpoint of the dumbbell.
Expert Tip: The Finishing Touch for Better Readability
- Layer Order: In Power BI's Line Chart, Error Bars overlap according to the order of the series. If the red (negative growth) should appear more clearly over the gray (normal), adjust the sequence of the series in the field pane.
- Visibility of Target Markers: Target markers may be obscured by the thick 10px dumbbell bar. In this case, slightly increase the size of the Target series in Format > Markers, or select a horizontal line (-) shape for the marker type to make it more visible even when overlapping the bar.
- Utilizing Tooltips: Instead of showing mere figures when hovering over the bar, you will provide a much more effective report for executives if you create a separate Growth Rate = ([TY]-[LY])/[LY] measure and include it in the Tooltips.
- Growth Amount = SUM('SalesData'[TY Actual (TY)]) - SUM('SalesData'[LY Actual (LY)])
- Growth Rate = DIVIDE( [Growth Amount], SUM('SalesData'[LY Actual (LY)]), 0)
Apply the DAX tricks and layout settings introduced today to your own business dashboards. I support your journey to becoming a BI expert who breathes life into numbers and delivers the insights hidden behind the data in the most sophisticated way.
Comments
Post a Comment