Introduction
When you want a quick way to display rank change, one option is Power BI’s Ribbon Chart. The Bump Chart is a common chart used for a similar purpose. Don’t be fooled by the complexity of the flow or ribbon in the Ribbon Chart. The Bump Chart is actually the more difficult chart to create in Power BI!
The Ribbon Chart visually has a lot going on, but simply drop in a few fields, and you’re done. The rank in this chart is displayed like a stacked column chart, with the highest rank value sorted to the top. The bar displays an actual measure value instead of simply relative rank. The ribbon between bars measures difference and has a helpful tooltip with additional data points. All that without much work on your part.
The Bump Chart, by contrast, has a few challenges in Power BI:
1. Can you build a DAX ranking measure that takes the place of the native ranking functionality of the Ribbon Chart?
2. The Power BI Line Chart currently can’t invert the Y Axis so that a rank value of 1 appears on top, but can you fake it?
3. The default Tooltip clearly shows the evidence of the common workaround for inverting the rank, so can you change how that displays?
Explore some of the differences between the charts in the sample below, and then try to rebuild your own version of both charts in Power BI Desktop. What are some pros and cons of both chart types?
Requirements
- Use the Financials sample dataset (available in Power BI Desktop from October 2020 onward)
- Create a Ribbon Chart with a combination of Date, Country, and Sales.
- Create a DAX measure that will rank countries by sales.
- Use a combination of DAX functions SUM, CALCULATE, ALLSELECTED, and RANKX in your measure.
- HINT: Go to the DAX Guide function reference for some examples of how these functions work in general.
- HINT HINT: Expand the Solution below if you get stuck and want a sample measure. If you’ve never touched DAX before, some of these functions can seem intimidating, and we don’t want that to stop you from building a visualization.
- Add a Line Chart that includes Date, Country, and your custom sales rank measure.
- Add markers to your line chart.
- Determine a way to make your sales rank on the line chart appear like the ribbon chart. The highest rank value should be at the top of the visual and not the bottom.
- If you had to modify your rank measure to fulfill the prior requirement, be sure that rank values in the line chart’s Y Axis and default Tooltip appear as positive numbers. Use only the existing three fields and no other visuals…
Dataset
Did you know that Power BI Desktop now includes a sample dataset?
This week’s challenge is based on the Financials sample available in Power BI Desktop from October 2020 onward.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
Solution
HINT: Sample ranking measure:
HINT: Sample DAX ranking measure:
RANKX(ALLSELECTED(financials[Country]),CALCULATE(SUM(financials[ Sales])))
What do y’all think of this?
STEP 1: I created a new table called CountriesandSales with the following AddColumns function:
CountriesAndSales =
ADDCOLUMNS(
SUMMARIZE(financials, financials[Country]),
“@Sales”, CALCULATE( SUM(financials[ Sales])))
STEP 2: I added a calculated RANK column with the following RANKX function:
RANK = RANKX(ALLSELECTED(CountriesAndSales),CountriesAndSales[@Sales])
I never used the ALLSELECTED function – what’s cool about it is that if you added a slicer to your canvas and filtered one of the countries, the country retains its ranked value. Without the ALLSELECTED function included around the table in the RANKX function, the rank changes based on the slicer selection.
Fascinating……🤔💭
This was a cool challenge. Never made either graphs before. Will there be a video explanation for this one? I’m curious how the ordering of the ranks was solved. I ended up using custom format and negative values (https://youtu.be/098v5K2LmTU)
Hi,
There is no solution on Youtube for this challenge?
Thanks.
Remi
Hi,
There is no solution on Youtube for this challenge?
Thanks.
Remi
That´s a good solution and thanks for what you found about the ALLSELECTED function and sharing that with us
Hi,
This was a really good challenge and I struggle with the country ranking code. The STEP 2 code gave me the overall country ranking (from 1 to 80). So I used the following one to create an extra column in the new table to sort out the country ranking per month:
RankColumn =
RANKX(
FILTER(CountriesAndSales,CountriesAndSales[Date]=EARLIER(CountriesAndSales[Date])), CountriesAndSales[@Sales],,DESC,Dense)
Hope it helps others.
Really enjoying these challenges as they are the best way to apply, practice, and learn new skills. Keep going the good work and the new challenges.
Good challenge.
Curious how you inverted the y-axis for rank.
But of course some time over teh past 18 months Power BI introduced an Invert Range toggle (Visual – y-axis – Range)
When plotting my measure, does anyone know why the bump chart plots my rankings starting from Jan 2013? The sales data starts from September 2023, so I do not understand why it starts plotting from Jan 1?
Does your date table go back to 2013?
It’s some time ago I looked at this problem, but yes, for some reason my data table started in 2013. I solved it by creating my own custom data table using the CALENDAR function so it made me learn even more DAX 🙂
Thanks for the challenge!
Solution to this challenge https://youtu.be/BwBogbb7L-o
1-> salesbycountry = CALCULATE(SUM(financials[ Sales]),ALLEXCEPT(financials,financials[Country]))
2-> Countriesbysales = RANKX(ALLSELECTED(financials[Country]),(CALCULATE(SUM(financials[ Sales]))),,ASC)
These two measures did the trick for me!