In this tutorial, we’ll discuss the not-so popular visual in Power BI called Dumbbell charts. A Dumbbell chart is commonly used to show comparison between two or more groups of data points. This is also sometimes called a DNA chart.
Here’s an example of a dumbbell chart. This is created with Charticulator, and not with via custom visual from the marketplace.
But for this tutorial, we’ll be using a dumbbell chart custom visual from the marketplace.
Creating Dumbbell Charts In Power BI
Click the 3 dots under the Visualizations pane, then select Get more visuals.
Search “dumbbell chart” in the search box, then add the Dumbbell Chart by MAQ Software.
After that, add it to our report page and resize it as shown in the image.
To use this visual, we must have the R script installed on our Power BI. To enable or install the R script, click File from the menu bar.
Go to Options and Settings, then click Options.
Select R scripting. Then, click the “How to install R” to install the R script. Make sure to restart Power BI after installing it.
In the Category field, let’s add Assignee. Just like what I mentioned in the Dot Plot tutorial, the Assignee is responsible for solving the issue created by the employees in the organization.
For the Measure field, let’s use the Avg Days To Resolve measure.
The output should now look like this. Different tools for zooming, panning, selecting, and other tools are also available.
Limitations Of Dumbbell Charts
1. Adding Two Categories Is Not Allowed
One of the limitations of this visual is that we won’t be able to add 2 or more categories. As you can see, upon adding the Year, the Assignee was automatically changed.
Let’s change this again to Assignee.
Let’s add another measure (Count of Tickets) in the Measure field. As you can see, this visual accepts multiple measures but not multiple categories.
This will result to this output.
We can start modifying the appearance of this chart by turning off the Background.
Under the Colors, we can change the Measure, Connector, Axis and Plot background colors.
Let’s make the background color of the Axis similar to the background color of our report page.
Next is to make the background color of Plot similar to the background color of our report page as well.
Let’s also change the color of the labels to white.
We can do the same modifications with the Y-axis. Let’s say we want to hide the Assignee title in this part.
We don’t have any options to turn off the Y-axis title. So, let’s just change the Title color to the color that’s similar to the background color.
As you can see, the Assignee title is not visible anymore. It’s still there but it looks hidden because it has the same color as the background.
Let’s also change the Label color to white.
2. There’s No Option To Change The Color Of The Legend
Another limitation of this visual is that we don’t have the option to change the color of the legend. So, let’s just turn it off instead.
However, we can make a measure that can define the colors of the dots. For example, if we hover over this portion, it indicates that Richard Hendricks took an average of 129 days to resolve the issues.
Then, if we hover over the red dot, we can see that there are 226 tickets assigned to him.
This visual will work out if we want to assign one point to a number of sales before the promotion discounts, then another point with a number of sales after the promotion discounts. We can also add another point for a number of sales during peak period, and during off peak periods.
3. Conflicts Arise When Using Power BI Slicers
Another problem with Dumbbell charts is that if we use a filter or slicer, categories that don’t have any value will disappear or change their position in the axis.
In this example, let’s add a slicer, and use the Priority measure. Then, let’s filter by Highest priority. As you can see, the categories that don’t have any value have disappeared and also changed the position of the axis. Unfortunately, Power BI doesn’t have the option to turn this function off.
This is the reason why I chose to create a dumbbell chart in Charticulator.
Dumbbell Charts In Charticulator
Here’s an example of a dumbbell chart that I created in Charticulator. If I slice the data in this chart, you’ll see that these categories will not change their position and will not disappear.
Once I click the Highest priority filter, you’ll see that the categories stayed in their position. This feature is very useful especially if we want to place static texts to tell the users some additional information.
Another thing with this visual that I created in the Charticulator is that it accepts multiple categories. As you can see, I’m categorizing it by Assignees and by Year.
***** Related Links *****
New Enterprise DNA Course: Advanced Visualization Techniques
Visualization Technique – Power BI Column Charts & Filters
Power BI Desktop Update: The Charticulator Visual
Conclusion
We’ve covered about dumbbell charts in this tutorial. We discussed how to create a dumbbell chart in Power BI custom visuals and its disadvantages. In Power BI, we can’t use two or more categories but it accepts multiple measures. Nevertheless, it’s still good that we can place multiple measures in this visual.
On the other hand, we can also create dumbbell charts in Charticulator. By doing it in Charticulator, we can freely use slicers without having to worry about conflicts or issues that the chart may exhibit.
Until next time,
Mudassir
