Throwing a Curve at the Power BI Line Chart

Last month, I explored how to take a Power BI area chart and make it into a unique “new” chart type using only data and DAX formulas. The line chart is also a candidate for a bit more flare.

How can we go from a bland line chart…

…to a more attractive, curved slopegraph?

Many in the Tableau community have been leveraging the sigmoid function to create curves in visualizations for a few years. While there are a number of posts on different ways to employ this particular curve equation, my inspiration for adapting the sigmoid to Power BI came from this post by Jeffrey Shaffer. In it, he walks through steps to create a “sankey-style slopegraph” in Tableau. While we cannot quite get the same output from Power BI, it’s still worth a look at what can be done as well as where Power BI users might run into trouble or need workarounds.

Before we get started in-depth, I want to note that there is a wealth of experimentation that can be done with data, formatting, and DAX formulas alone to do new things with existing visuals. As with the horizon graph, this is another instance where we see how to take an existing visual (the line chart) and do something new without having to build and import a custom visual.

Also, the point of this post is not meant to debate the use of curves versus straight lines from a data visualization perspective. Rather, it’s simply a “how-to”.

Build a Curve

Start with sample data shaped like the following simple example, where there is a separate column for the initial (left-hand) value and final (right-hand) value. In this case, the sample consists of one baseball division and what place the teams finished in 2015 and 2016.

There is also a separate source for a sequence between -6 and 6 at increments of 0.25 (49 total values).  This sequence functions as an artificial axis for the line chart. As Jeff explains in his post, this number is arbitrary but provides enough points in the line to make it appear as a smooth curve.

This sample file provides an Excel source that matches the following steps. There are two sheets: one with the data to visualize (ValueTable) and one containing the sequence (Sequence).

Import the two sheets into Power BI. The sequence exists as a disconnected table in Power BI, so there is no relationship between the values table and sequence table.

Next, add two new measures called Curve Function and Curve to the ValueTable.

Curve Function = 1/(1+EXP(-LASTNONBLANK('Sequence'[t],1)))

Curve = (LASTNONBLANK('ValueTable'[Initial Value],1) + ((LASTNONBLANK('ValueTable'[Final Value],1)-LASTNONBLANK('ValueTable'[Initial Value],1))*[Curve Function])) * -1

Curve Function defines the shape of the curve at any point along the sequence. This example uses a DAX adaptation of the sigmoid function.

Curve unites the previously defined Curve Function measure with your data’s initial and final values. The * -1 exists at the end because Power BI does not allow the Y Axis on a line chart to be sorted by our desired rank, and we need to reverse it. We want the highest rank of “1” to appear at the top and not bottom of the line chart.

At this stage, the Fields pane should appear like this:

Add a Line Chart to the report, then place ‘Sequence'[t] on Axis, ‘ValueTable'[Category] on Legend, and ‘ValueTable’[Curve] on Values.

Once the data has been added, the chart should appear like this:

From here, it’s all formatting. Go to Format and do the following to get a cleaner visual:

1. Turn the Legend Off
2. Turn the Y Axis Off
3. Turn the X Axis Off
4. Turn the Title Off

Next, under Format, change the Data Colors to whatever you want (or import a Theme). You can also change the Stroke Width under Shapes.

For the MLB sample visual, I changed the colors to correspond to each team’s colors. The double color effect was accomplished by simply copying and pasting my line chart to create a second chart, then shifting the overlaid chart up and changing the colors.

The team labels to the right are not part of any default line chart labels (that’s a grievance for another day). To get the labels, one data-driven alternative you could use would be to add a bar chart, change the default bar color to match the background, and send it to the back so that it appears behind the line chart(s). It may take some resizing to get the labels to appear as if they fit with the main line chart.

After adding textbox labels and a title, the final visualization takes shape.

You can get a completed PBIX file to help reverse-engineer and explore on your own from my post on the Power BI Data Stories Gallery (and also give Kudos!).

Crossfiltering

If you would like to add other visuals to the report, crossfiltering will be an issue since the line chart does not allow highlighting. In this case, an overlay can be beneficial. This technique works for any line chart by the way, not just this curved one. By disabling the visual interaction for the “underlay” and setting a static Y Axis range for the overlay but keeping the default interaction set to filter, you can get a highlighting effect. I also reduced the line stroke width for the bottom chart so that the overlay is emphasized when I click something on another visual.

Alternative Equations

You can also reverse the sort so that the final value appears first if desired by removing the minus sign from the sigmoid function:

Left Side = Initial Value, flows from left to right

Curve Function = 1/(1+EXP(1)^-LASTNONBLANK('Sequence'[t],1))

Right Side = Initial Value, flows from right to left

Curve Function = 1/(1+EXP(1)^LASTNONBLANK('Sequence'[t],1))

In addition, you can explore other curve equations to get different visual effects:

Curve Function = 1/(1+EXP(ABS(LASTNONBLANK('Sequence'[t],1)))^-LASTNONBLANK('Sequence'[t],1))

Curve Function = 1/(1+EXP(ABS(LASTNONBLANK('Sequence'[t],1)/10))^-LASTNONBLANK('Sequence'[t],1))

Curve Function = TANH(LASTNONBLANK('Sequence'[t],1))

What’s Less than Ideal?

With this curvature possible in Power BI, would anyone use it? Without even discussing the merits from a data visualization perspective, there are software-related reasons why it may be tough to implement this for a wider audience. In my opinion, there are two main factors that currently make this less than ideal:

1. Tooltip (two reasons)

a) Power BI does not allow you to control what defaults appear on the tooltip or disable the tooltip if desired (please vote for that capability here). With your “t” sequence as the axis, hovering over the chart provides data for the sequence and curve that doesn’t mean anything to viewers. Meaningful numbers can be added to the tooltip, but there is no way to remove or customize these initial values.

b) There is also no setting for the tooltip to limit the display to only what you hover over. For example, with a line chart containing five categories, if you hover over a single line, you get data for all five categories.

2. No data-driven line stroke width

Line stroke width generally is not an issue if you only want to visualize flow. Sometimes though, as in a Sankey diagram, there is measurable data associated with that flow where a data-driven width would be beneficial.

Workaround: With some of these limitations in play, I expanded the title textbox in my report so that it overlaid the line chart. This effectively “disables” the tooltip. It’s better for users to see no detailed values than confusing or meaningless ones.