Calculate Median Aspect Ratio with Power Query

I wish I had something witty to say for a caption, but I am mildly terrified of this image lol

Kevin Flerlage posted an article about truncating axes on line charts and a really good walkthrough of the issue and ways to answer this question. Me being me, of course I went down a tangential path based on an algorithm quoted in the article.

Kevin linked to this article: https://blogs.sas.com/content/iml/2016/01/20/banking-to-45-aspect-ratio-time-series.html. The article goes through some algorithms you can use in SAS to calculate a useable aspect ratio for your visual based on your data.

There were three Banking Methods discussed in the article. Two took advantage of the FROOT function in SAS. I have never used SAS, so I did not spend my time trying to figure out those two (Average Orientation and Average Weighted Orientation).

I was able to replicate the Median Slope method with Power Query though!

The Code

Here is the code (and the gist):

(x as list, y as list) =>
  let
    // Get the range of the x series/column by subtracting max from min
    range_x = List.Max(x) - List.Min(x), 
    // Get the range of the y series/column by subtracting max from min
    range_y = List.Max(y) - List.Min(y), 
    // Get max index of one of the columns for List.Generate
    max = List.Count(x) - 1, 
    // The equation asks for running differences (think lag in SQL) divided by the range for x and y
    acc = List.Generate(
      () => [i = 0, dx = 0, dy = 0], 
      each [i] < max, 
      each [i = [i] + 1, dx = (x{[i] + 1} - x{[i]}) / range_x, dy = (y{[i] + 1} - y{[i]}) / range_y]
    ), 
    // Then divide each dx and dy. After that, take the absolute value
    div = List.Transform(
      List.Skip(acc, 1), 
      each Number.Abs(Record.Field(_, "dy") / Record.Field(_, "dx"))
    ), 
    // Get the median of those values
    median = List.Median(div), 
    // Get aspect ratio
    a = (1 / median)
  in
    // Return a record with aspect ratio and median absolute slope
    [aspect_ratio = a, median_slope = median]

How This Works

The main part of the function is that you have to compute a running difference for the x and y values to start. For SQL folks, think LAG(). In Power Query, I went with List.Generate() and made nested records. Why? It’s easier to debug, at least for me.

Once I was able to generate a list of running differences, the rest was simpler. You needed to divide each number by the range of the variable (which gave you dx, dy) and then later divide each dy by dx.

If you have ever done a LAG() query, then you would know that the first value is always null (because there is no previous record to operate on). Hence, the List.Skip(…, 1), which skips the first null value.

After that it was a few simple calculations, like finding median slope and also returning one divided by the median, which was the aspect ratio. The result is a record with the median slope and the aspect ratio.

Replication

I found the melanoma dataset mentioned and was able to get the same values:

Applying the aspect ratio (multiplying width by 0.37 to get height) to a chart in Excel gave me something like this:

Looks pretty good!

Now, there apparently is a lot of research on this topic, so I am not sure what the current research says, but this seems like a pretty decent way of checking if your visual is way off or not.