Dumbbell charts are a great way to visualize the differences between two data points across categories, such as gender distribution by age group. However, Power BI doesn’t offer a built-in option for dumbbell charts, so we need to get a bit creative! In this guide, I’ll walk you through how to create a dumbbell chart using DAX and a line chart.
- First, choose the line chart visualization. Then, create a dummy X-axis to serve as the basis for our chart. Go to Modeling > New Parameter > Numeric Range, and configure it as shown in the screenshot below. Once created, drag this new parameter to the line chart.
- To visualise gender distribution, calculate the percentage for each gender. Start with the male percentage calculation, and then repeat the process for female percentages. This will help establish the two points on the dumbbell chart.
- For the dots in the line chart, create a DAX formula as below picture, this calculation will show only 1 data point in which the percentage calculation is rounded up to match the numeric data type of our X-axis. Be sure to apply this calculation for both genders.
- To complete the dumbbell effect, we’ll create a connecting line between the two dots. Use below DAX calculation that returns a line, beginning from the minimum value and extending to the maximum value between genders.
- Finally, bring all your measures onto the line chart. To separate the data by age group, use the “Small Multiples” option in Power BI.
You may first end up with this layout:
However, you can adjust the layout to reduce the number of columns to 1 and increase the rows to fit your categories. You can also refine the look further by removing borders and axis as needed.
Your chart should now look something like this, displaying data vertically down the page for each age group. Experiment with other customisation options, like color schemes and label placements, to make your visualisation as clear and appealing as possible. And there you have it!
Thanks for reading, and I hope this guide helps you create your own dumbbell charts in Power BI. Happy charting!
