1. IDENTIFY THE CATEGORY AND MEASURE
The first step in creating a bump chart in Power BI is identifying the category we want to rank and the measure we want to base the rank on. Let’s take a look at my mock data:
For this example, I want to know which ‘Region’ ranks the highest the highest in a given time period in total sales.
2. CREATE A “MEASURE FOR YOUR MEASURE”
In Power BI, We will need to first create a measure for the number column that you’d like to rank your category off of. The formula for this measure is:
MeasureName = SUM(TableName[MeasureColumnName])
In this example my measure is:
Sum of Sales = SUM(Orders[Sales])
3. RANKX
In order to rank the Regions by Sales, we need to create a measure using the DAX function RANKX. The calculation looks something like this:
Rank = IF(HASONEVALUE(TableName[CategoryColumnName]),
RANKX(ALLSELECTED(TableName[CategoryColumnName]), [MeasureName]))
Our ranking calculation in this example is:
Region Rank = IF(HASONEVALUE(Orders[Region]),
RANKX(ALLSELECTED(Orders[Region]), [Sum of Sales]))
4. BUILDING THE BUMP CHART in Power BI
Start by creating a line chart and add the Date column to the Axis, the Region Rank Measure to the Values, and the Region column to the Legend. You will see the beginnings of our bump chart created:
5. TIME FOR SOME MAGIC
There are three things we need to do to in order to complete our bump chart. First, we see that our current bump chart is reversed. The rank “1” is at the bottom and rank “4” is at the top. To remedy this, we need to multiply our Region Rank Measure by -1. So I’m going to tack on a “* -1” to the end of our Region Rank Measure:
Now that our bump chart is right-side-up we can do two more things: hide the Y axis and make the stroke size larger. In the formatting pane, switch the Y axis to “off” and in the Shapes menu, change the stroke width to a larger number to make the lines stand out more.
We now have a decent-looking bump chart! We can now edit the colors if desired and add to a report for some deeper insights! Hope this helped you out on your quest to bump chart-dom!
If you have any questions about how Power BI can get you data insights fast, reach out to us today!
[maxbutton id=”1″]
Matt Winter
Analytics Associate
About Matt: Matt leverages Business Intelligence tools with his background in Educational Measurement to empower businesses to make data-driven decisions. Recently, his area of work has included the banking, pharmaceutical, nonprofit, and manufacturing industries.
Matt received a B.A. in Chinese from California State University Los Angeles, an M.A. in Chinese language pedagogy from Indiana University, and an Ed.M. in Applied Linguistics, Second Language Assessment tract, from Columbia University. Matt is certified in Tableau, Spotfire, Power BI, and RapidMiner.
