Facebooktwittergoogle_plusredditpinterestlinkedinmail

Capture

Bump charts are a visually appealing way to view a rank over time. Although not native to Power BI, bump charts can be created using the RANKX function in DAX in a line chart and with a few tricks up our sleeve.

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:

01

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

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:

Capture2

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:

add

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.

Capture3

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!

4


6

If you have any questions about how Power BI can get you data insights fast, reach out to us today!

CONTACT US

matt profile croppedMatt 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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Categories: Tip of the Week

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

blog

XBRL Automation for Financial Analysis

There is a common misconception about the costs associated with structuring data for financial filings to regulators. Companies assume that converting financial reports to XBRL a high effort / low value expense. But the lack Read more…

Tip of the Week

Up And Running With Row-Level Security In Power BI

It has quickly become the norm for companies to maximize their data potential by using data in their everyday business processes. And as your data grows and users increase, the need for limiting data to Read more…

blog

Summer Intern Perspective – Pt. 2

Another one of our interns, Sharat Vyas, describes his experience of doing a summer internship with Syntelli.              This past summer, I have had the privilege of interning at Syntelli Read more…

 

Login

Register | Lost your password?