For our third week at the Data School, we learned about LOD (Level of Detail) and table calculations in Tableau. These concepts are powerful tools that allow us to perform more complex analyses with ease. For this week’s Friday challenge, we were tasked with completing a Workout Wednesday challenge involving one of these techniques.
I chose to do this LOD challenge that requires to create a chart comparing the sales of the most month to the average sales over a specified number of previous months. Initially, I tried to tackle the problem using LOD calculations but found myself stuck. Thankfully, Beth, pointed me in the right direction by suggesting that table calculations would be more suitable for this task.
Let me walk you through how I approached and solved this challenge.
1. Start by setting up your sheet with Region and SUM(Sales) to the Rows, and MONTH(Order Date) to the Columns. Then, change the chart into bar, so it will look like below.
2. Then to calculate the latest month of sales, we need to isolate the last month first using LAST( ) function. Then create below calculated field to return only the sales of that month, in which LAST ( ) = 0.
3. Next, duplicate the Sales field and apply a quick table calculation > moving average. When you edit the table calculation, you’ll notice it averages the sales for the previous two months and including the current month by default
4. Since we want our calculation to be dynamic, we’ll create a parameter that allows the user to select the number of months to include in the average. Set the minimum value to 2, as we need at least two months of data to calculate an average.
5. Now to integrate the Parameter into the moving average, drag the SUM(Sales) field with the table calculation into the field list to create a calculated field. Modify the calculation to incorporate the parameter for the start, and -1 for the end to exclude the most recent month’s value, as we will be comparing to the last month.
6. To visually highlight whether the latest month’s sales are higher or lower than the average, create a Boolean calculated field. Bring this calculated field into the colour card of your SUM(Sales).
Finally, Then, change the moving average line into a line chart, and set up a dual axis to overlay both the sales and moving average trends. Be sure to synchronize the axes for consistency. And that’s it—your dynamic comparison chart is ready! This challenge was a great learning experience in using table calculations to create dynamic visualisation for me. You can see the final result on my Tableau page here.
Thanks for following along, and I hope this walkthrough helps you in your own Tableau journey!