Data Guardrails

If you've ever driven down a terrifying highway where the road drops off the side of a cliff on one side and a rocky mountain cuts all sunlight off on the other side, the guardrails on one (or both) sides of the road provide a comforting feeling along the way. At the same time, a road without guardrails (see the article's cover image) has the opposite effect.

The same goes for data! Whether it's examining past trends or forecasting future numbers, guardrails around the numbers give us a cushion for the variation of data points without throwing the whole path off track. Examples of these data guardrails include confidence intervals, which also include the topic of this week's newsletter: error bars!

Power BI Weekly

Within the last year, Power BI started offering us the capability to enable error bars on many of its standard visuals. These analytics aren't available on every type of visual, but for those visuals that do support error bars, this functionality can be a game changer. I'd be very happy even if this option was only available on a few bar and column charts! In a recent video of the Power BI Weekly series, I walk through an example of how to use these error bars to add insights and analysis to a data visualization.

Error Bar Examples

While error bars serve an immensely useful role in analyzing data insights, they don't necessarily work for every data set. I recommend checking out the Power BI Weekly video as a starting point to try out error bars. They're in the preview features mode right now, but they should be a standard part of Power BI soon. So, what are some other examples of how we can use error bars as an impactful additional to our data analysis?

Volatile Prices

For anyone who works with any type of market prices (stocks, currency exchanges, and so on), you'll know how these markets are more often than not volatile ones. Commodities like energy prices (for all types of fuels, including fossil fuels), provide great examples of how error bars can provide clear insights to cut through much of the ambiguity of the variance. The WTI price we see below is the daily spot price for crude oil out of Cushing, Oklahoma. Because it's a spot price, it's typically more volatile than stock market prices because it's traded on the energy futures market for delivery in the future.

The line chart below illustrates just how volatile this particular spot price is (the Brent spot price is another comparison). I personally don't do much analysis in this part of the energy market (the electricity grid data is another story), but I chose the WTI spot price as an example in a CODE Magazine article I wrote last year because it is quite volatile (it actually does go below zero in March 2020 in the early months of the pandemic). I created the visuals below using the ggplot2 library in the Power BI R visual, but with the recent addition of the error bars functionality directly in Power BI, we no longer have to write R code to create this visual!

There's quite a bit of analysis we can do on even just a single commodity price like this. In the bottom column chart in this view, we can see the average prices for an entire year represented in the height of each individual blue column. On the same vertical axis, we can also compare the variance within the same year using the error bars. This particular example is done in R before the error bar option was available in Power BI. The distance between the bottom and the top of each individual error bar represents the 95% confidence interval for the year. The combination of the column/bar and the error bar in each year summarize both the average and the variance of the spot price data points into an aggregated view that's much easier to clearly communicate to a wider audience.

Budgets vs. Actuals

Another data analytics example where I found error bars helpful is for modeling comparison points for numbers. A great example of this is in the financial analysis space for comparing summarized total numbers for actual versus budget amounts. In this completely fictitious example that I made up in the chart below, we see the revenues modeled in blue and the expenses modeled in orange. The side-by-side comparison of the revenues and expenses next to each other for each year lets us easily determine at first glance whether or not the year was a profitable one or not (again, the visual uses fictitious data points, but it gives us a good example of how to make a comparison like this).

I made the error bars by setting a DAX measure for the budgets for both the revenues and expenses, then adding the same exact measure to both the high and low bounds in the error bar configuration options. This essentially removes the middle range of the error bars, so they appear as a single bar for each individual column. I then formatted the error bar appearance to scale their size (we can also apply additional formatting changes, like changing the colors within the error bars).

I shared this visual above recently in a LinkedIn post. Someone asked me in one of the comments why I didn't use the custom bullet chart visual available in the AppSource store. There are a few reasons I prefer the approach above. First, I'm not crazy about the configuration and formatting options for the custom bullet chart. Second, I've encountered quite a few organizations who are averse to using custom Power BI visuals in their enterprise cloud accounts (or servers). I understand their rationale for avoiding them and understand why they're sticking to that path. So, in that vein of thinking, I recommend checking out the error bars as a great alternative for those working strickly with the built-in standard Power BI visuals!

Other News!

If you're looking to explore the integration of data science and Power BI, check out my most recent CODE Magazine article! I used the graphic on the right as a reference guide for the article because it enables us to categorize the functionalities within Power BI along the way. For more in-depth examples of how to apply these algorithms without writing any code, check out my LinkedIn Learning course on this topic!

I'll also be doing a presentation at the virtual My Data Summit event next week on how Python and R code can complement Power BI. Please register if you want to check out my presentation or any of the others at the event.

Up in the next edition of this newsletter: time efficiency in Power BI (in three different ways)! Stay tuned and subscribe to this newsletter if you haven't already!

-HW