Building Horizon Graphs in Power BI

Horizon graphs are an intriguing but initially confusing way of looking at time series. Once you get familiar with the layout, you can simply think of it as a more space-efficient way of rendering a common line or area chart. Where there is a broad range in the data from low values to high values, horizon graphs condense the view while preserving the ability to view peaks and troughs.

Horizon graphs were created in the last ten years by Panopticon and even received some early critical praise. Since the focus of this post is simply creating them, you can read about their strengths and history in more detail by following those two links.

Can Power BI render horizon graphs? The challenge that I recently faced was to answer this question without attempting a custom visual. D3 has a horizon plugin, but it is much more convenient if it is possible to use native visuals.

In the following example, the two visuals display the difference between a standard area chart and a corresponding horizon graph. In the horizon chart on the bottom, the three colored bands share the same Y axis. The lighter the color, the lower the value. Note how the lighter bands often get “clipped” at the top as the peaks restart at the bottom, but in a darker color.

Not a Horizon Graph

PBI-HorizonUnhorizoned.PNG

 

More or Less a Horizon Graph

PBI-HorizonSample

 

The secret comes down to the data–not the visual. A horizon graph is nothing more than a shaded area chart. The unique display comes from the bands, and in Power BI, you can use DAX to get the data in the right form to turn a simple area chart into something that approximates a horizon graph.

Here are the steps I used to create a basic horizon graph in Power BI:

  1. Create a measure that represents the maximum value over time. This will be the highest peak in a standard line chart. In the case of my sample data, I’m looking at crimes in Detroit throughout 2016. I want a measure that will get the highest count by time period. I named this measure Max Value.PBI-HorizonMaxValueOverTime
  2. Select the number of bands. In this case, I chose three, and I created three measures to act as the upper limit for the Y axis for each band. The existing Max Value acts as the upper limit for the top band, while two new measures act as the upper limit for the lowest and middle bands. Since there are three bands, I divided by 3 and 1.5 to evenly split the bands into thirds.PBI-HorizonMiddleBandMaxPBI-HorizonLowestBandMax
  3. Create a measure for the lowest band. This represents data from 0 to 1/3 of the max value. For example, if the maximum Y value is 300, this lowest tier would fall between 0-100. If the data were filtered and a new max occurred at 150, the lowest tier would fall between 0-50, and so on.  I did not attempt to account for negative values, but that could be done as part of additional attempts. Note that the DAX formula also introduces [Horizon Measure], which can be whatever value you are representing on the chart. If the Horizon Measure is greater than the Lowest Band Max, it uses the band’s max instead. This is what “clips” the peaks in that band.PBI-HorizonLowestBand
  4. Create a measure for the middle band. The conditional logic is different but similarly attempts to either set the value to a new ceiling if it exceeds the band’s max or a new floor if it is lower than the lowest band’s max.PBI-HorizonMiddleBand
  5. Create a measure for the top band.PBI-HorizonTopBand
  6. Add an Area Chart to the report canvas. Add your date column to the Axis, and then add the three band measures to Values. In the screenshot below, I have Lowest BandMiddle Band, and Top Band together, and I also added the primary Horizon Measure as a Tooltip. An unfortunate side effect of having the band measures involved is that they will appear in the tooltip and cannot be hidden, so I labeled them as “lightest” or “darkest” to try to make them appear helpful. Lowest + Middle + Top should equal the value for the main measure.PBI-HorizonAreaChart
  7. Select Format and change the colors to your desired scale or saturation. I used a lighter color to represent the lowest band and a darker color to represent the top band. You should also disable the Y Axis since the value you care about is the main Horizon Measure and not the artificial 1/3 band limit.PBI-Formatting.PNG

Here’s a view of the horizon graph interacting with other visual selections, and you can also play with a live version that I created with Publish to Web.

Horizon.gif

Is it a perfect Horizon Graph? No, far from it. With this first iteration, it was easy enough to get the basic look. Showing separate graphs for comparing different categories will be a pain since Power BI does not provide well for small multiples. Accounting for negative values may be challenging as well. In any case, it was a rewarding first attempt at a unique and uncommon visual that you can somewhat achieve using Power BI’s standard Area Chart.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s