Power BI Tips and Tricks: Reversing the Y-Axis in a Line Chart Visual?

[PaulDBrown]

Background

I was recently involved in a project to study the quality of MBAs offered by the leading business schools in the world. The source data  provided a ranking for both overall performance and for specific areas affecting a student’s experience during the course, and I thought a visual representing how the ranks evolved over the years would be a useful insight for the users, since it would provide information of how the course was perceived as a trend.

The challenge: how do we plot a rank value over time?

Calculating the actual ranks is not particularly difficult once we master the function RANKX. The problem really arises when we want to display the rank in a line chart or visual (especially over time). The top-ranking product we of course define as number 1, and the way we perceive ranks is in “ascending” order, where “top” is 1 and the worst performer is actually the highest number. If we simply plot the rank values in a line chart, we will actually “see” the top-ranking products (1, 2, 3…) at the bottom of the Y axis and the worst ranking products higher up. This is counterintuitive since we expect to see high ranking products “higher” than low ranking products.

So how do we “reverse” the Y-axis to display lower values (better ranking values) higher?

The default line chart in Power BI does not offer this functionality. There are I believe a “couple” of custom visuals which do offer this, but sometimes custom visuals are not an option. How can we achieve this in the default line visual within Power BI?

A solution (there are normally more than one way of doing things in Power BI of course…)

A simple solution is to simply multiply the rank value by -1. If we then plot these negative values (and since they will all be negative) the visual will actually display the higher rank values (-1, -2, -3….) “higher up”, since they are, well, higher values!

This gets us halfway to solving the challenge. The problem now arises that if we add labels to the points on the visual or values to the axis, they will be displayed as negative integers, which is a nuisance.

We cannot use a function such as ABS (which converts numbers to absolute values) since the actual value is then rendered “positive” and we are back to square one. What we need is to be able to use the negative rank value, but format it to display as a “positive” integer in the actual visual (for the labels or the axis values). There are probably a number of ways to achieve this, but the simplest I’ve found is to use the measure formatting options in the modelling pane in Power BI Desktop. This functionality allows us to format a measure’s both positive (the first expression in the format string) and negative values (the second expression in the format string) to suit our users’ preferences. We can choose from presets, or actually write our own custom format.

We can therefore easily establish how we want negative values to be displayed. All we need to do for this challenge is set the expression for the negative value to omit the “-“ symbol and we’re done!

The actual values of the measure are unaffected (the negative values are still negative – you can check by creating a new measure and multiplying the formatted measure x 1); all that has changed is how the value is actually displayed (format).

If we now plot these values in the line chart, we get the desired result whereby the top ranking products are now displayed higher, which is what we would expect to see for ranks (a bit like a sports podium perhaps?)

Enjoy Power BI!