Power BI Dashboard Tutorial: Year over Year Difference Analysis

I want you to increase your efficiency and to stop using spreadsheets for every single analysis.

Everybody works with time series data at some point in time. Year over year (also known as YoY) analysis is one of the most useful analyses you can perform to determine changes, analyze growth and recognize trends in quantity on an annual basis.

Unfortunately, most data preparers are used to performing some unaesthetic flavor of this analysis using only Excel (looking at you FP&A). Without the benefit of using visualization to easily recognize trends, data consumers are forced to work harder to tease out the most salient information.

If you have access to Power BI Desktop (available for free), then you can perform a tabular year over year difference calculation and then tie that information to a bar chart that will help you visualize the variances.

In this video I will show you how to create a calendar table in DAX (Microsoft’s formula expression language) and use that table to enable a year over year analysis of customer orders at fictional Stark Industries. You don’t need to be an expert in DAX to take advantage, just type in the date calendar formula you see in the video and tweak the simple calculations to fit your data.

You could obviously perform a simple YoY analysis in Excel, but I want you to stay relevant and learn something new!

If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

 

Advertisements

Tableau Dashboard Tutorial: Dot Strip Plot

In this video tutorial I describe a dashboard that I put together that displays the distribution of various NBA player statistics. I use the always handy parameter to enable the user to choose which statistics are displayed on the dashboard. Although I’m showing sports statistics measures in this dashboard, it could easily be repurposed to show the distribution of a variety of business related metrics.

I break the dashboard up into three areas: histogram, dot strip plot, and heat map. In the second part of the video, I describe in detail how to build out a jittered dot strip plot. The benefit of the jittered dot strip plot is that the marks representing NBA players obstruct each other much less as compared to the linear dot strip plot.

Techniques used in the dashboard were previous outlined in my Ultimate Slope Graph and How to Use Jittering in Tableau (Scattered Data Points) posts.

Feel free to head to my Tableau Public page and download the workbook for yourself. Drop me a line in the comments or on YouTube if you learned something.

As always, do great things with your data!

If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Create Rounded Bar Charts in Tableau

Part 1: How to Make Rounded Bar Charts in Tableau

In this post you’re getting two videos for the price of one (considering they’re all free for now, that’s a good thing). I put together a relatively simple dashboard to help illustrate a few intermediate level concepts. In this first video I take a look at the number of total assists by NBA players during the 2017-2018 season. In case you were wondering, Russell Westbrook led the league in assists during that season. If you don’t know who Russell Westbrook is, then skip this Tableau stuff and watch the last video immediately (and then come back to the Tableau stuff).

In the first Tableau dashboard video, you’ll learn two concepts:

  • How to make rounded bar charts;
  • How to filter the number of bar chart marks via use of a parameter;

Part 2: Apply Custom Sorting in Tableau

In the second video I build upon the dashboard built in the first video by showing you how to add a custom sort. The custom sort relies upon the creation of a parameter and a calculated field. The parameter and calculated field enable the user to select either a dimension (e.g., Player Name) or a measure (e.g., sum of assists) from a drop down box and the visualization will sort ascending or descending as requested.

The calculated field relies upon the RANK_UNIQUE function.

In this context, RANK_UNIQUE returns the unique rank of each player’s assist total. The key with RANK_UNIQUE is that identical values are assigned different ranks. As an example, the set of values (6, 9, 9, 14) would be ranked (4, 2, 3, 1), as no tied rankings are allowed.

Part 3: Interact with the Dashboard

Bonus: Russell Westbrook on the Attack

For those of you who do not know who Russell Westbrook is, I’ve got you covered. These aren’t assists but in these situations, he didn’t need to pass!

References:

Thanks to both the Tableau Magic blog for outlining the concept of rounded bar charts and the VizJockey blog for the custom sort methodology. Check out and support these  blogs!

As always, do great things with your data!

If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

The Ultimate Tableau Slope Graph Video

In this video I tackle the subject of slope graphs also known as slope charts. I had some fun putting together this dashboard that illustrates the changes in wins for NBA teams during the 2016-2017 and 2017-2018 seasons. From the video you’ll discover that Chicago, Atlanta and Memphis are on a Hindenburg-like trajectory, while trusting the process in Philadelphia led to huge season gains in overall wins.

Here’s what you will learn from this video:

  • How to create a parameter that enables a user to select which win statistic measure to visualize;
  • How to use Table calculations like, LOOKUP(), FIRST() and LAST() to calculate period over period change;
  • How the impact of Mike Conley’s injury affected the Memphis Grizzlies last season;

Click the pic to interact with the Tableau Public visualization, also download the workbook and data to dissect as needed.

For your convenience the calculated fields that I used to create the measures are listed here. Note that [Selected Measure] is a parameter that you need to create that lists all of the measures.

Calc Select Measure
CASE [Selected Measure]
WHEN “Home Losses” Then [Home Losses]
WHEN “Home Wins” Then [Home Wins]
WHEN “Overall Losses” Then [Overall Losses]
WHEN “Overall Wins” Then [Overall Wins]
WHEN “Road Losses” Then [Road Losses]
WHEN “Road Wins” Then [Road Wins]
WHEN “vs East Conf Losses” Then [vs East Conf Losses]
WHEN “vs East Conf Wins” Then [vs East Conf Wins]
WHEN “vs West Conf Losses” Then [vs West Conf Losses]
WHEN “vs West Conf Wins” Then [vs West Conf Wins]

END
Better or Worse
IF [Selected Measure] = “Home Wins” OR
[Selected Measure] = “Overall Wins” OR
[Selected Measure] = “Road Wins” OR
[Selected Measure] = “vs East Conf Wins” OR
[Selected Measure] = “vs West Conf Wins”
THEN
//WIN MEASURES: Negative delta treated as “WORSE”, Positive delta treated as “BETTER”
(IF [Delta] < 0 THEN “WORSE” ELSEIF [Delta] = 0 THEN “SAME” ELSE “BETTER” END)
ELSE
//LOSS MEASURES: Positive delta treated as “WORSE” (more losses are worse), Negative delta treated as “BETTER”
(IF [Delta] > 0 THEN “WORSE” ELSEIF [Delta] = 0 THEN “SAME” ELSE “BETTER” END)
END
Delta
LOOKUP(SUM([Calc Select Measure]),LAST()) – LOOKUP(SUM([Calc Select Measure]),FIRST())
Delta ABS Value
ABS(LOOKUP(SUM([Calc Select Measure]),LAST()) – LOOKUP(SUM([Calc Select Measure]),FIRST()))
ToolTip
<Team> Trend: <AGG(Better or Worse)> by <AGG(Delta ABS Value)>
During the <Season> Season, the <Team> had <SUM(Calc Select Measure)> <Parameters.Selected Measure>.

I have to give thanks to Ben Jones at the Data Remixed blog for the inspiration!

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

How to Compare Actuals vs. Forecast in Tableau

Forecasting in Tableau uses a technique known as exponential smoothing. This is when an algorithm tries to find a regular pattern in your data that can be continued into the future.

In this video I’ll share some helpful tips to help you determine which options you should select that will enable Tableau to make the most predictive forecast for your data. By the end of the video you will be able to differentiate between an additive and multiplicative data pattern and to evaluate MASE to measure the accuracy of the forecast.

I’m not talking about this Mase:

Harlem World

Rather, you’ll learn about the mean absolute scaled error (i.e., MASE) and how it helps you judge the quality of the model.

In addition, you’ll also also learn how to compare your actual data to the Tableau forecast in order to judge if the model is doing its job.

If you’ve used the forecasting capabilities in Tableau without knowing about these concepts, you might have generated an inaccurate error riddled forecast. Don’t just set a forecast and forget it. Watch this video and generate better forecasts in Tableau!

Here is additional reading from Tableau on the forecast descriptions (including MASE).

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

How to Generate a Forecast in Power BI

In this video I’ll demonstrate how to use the forecasting analytics option in Power BI. Although Power BI’s forecast algorithm is a black box, it’s more than likely using exponential smoothing to generate results. At a very high level, exponential smoothing is an algorithm that looks for patterns in data and extrapolates that pattern into the future. To help exponential smoothing perform at an optimal level, it is very important to pick an accurate seasonality estimation, as this will have an outsized effect on the time series forecast.

If your data points are at the daily grain, then you’d use 365 as your seasonality value. If your data points are at a monthly grain, then you’d use 12 as your seasonality value. Generally, the more seasonality cycles (e.g., years) that you provide Power BI, the more predictive your forecast will be.

Without giving away the whole video, here is a pro and a con of using forecasting in Power BI.

Con: As I stated earlier the exact algorithm is a black box. Although based upon a Power View blog post, we can reasonably assume exponential smoothing is involved. Furthermore, the results cannot be exported into a spreadsheet and analyzed.

Pro: The ability to “hindcast” allows you to observe if the forecasted values match your actual values. This ability allows you to judge whether the forecast is performing well.

Check out the video; I predict you’ll learn something new.

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Row and Column Highlighting in Tableau

In this post you’ll learn how to highlight values in your Tableau table using set actions. The dashboard in this video displays the number of total points scored by NBA teams by position in the 2017-2018 season. I will give you step by step instructions on how to implement row and column highlighting on this dataset downloaded from basketballreference.com.

I’ve only made a few minor tweaks but this technique was developed by Tableau Zen Master Matt Chambers. You can check out his blog at sirvizalot.com and follow him at Big shout out to Matt for sharing this technique with the Tableau community!

You can interact with my visualization on Tableau Public:

If you find this type of instruction valuable make sure to subscribe to my Youtube channel!