Parameters & Window Calculations in Tableau
Ever wonder what are some use cases for parameters and window calculations in Tableau? This tutorial will show you how to utilize them using a simple example. We will be utilizing a dataset from Monday Makeover Week 25 for this tutorial.
Link to dataset:
https://data.world/makeovermonday/2020w25-amazons-tiny-profits-explained
For this tutorial, I will be focusing on guiding you towards achieving the results as shown below.
As you can see in the GIF shown, users can select their own input on the percentage of sales they want to see. By plotting the partial sales line together with profits over the years, users can see how many times profits surpass the partial sales line over the years.
For e.g, if the user selects ‘4.0%’ as an input, the graph will be updated to show that amazon profits have surpassed 4% margins 9 times from 2005.
Step 1: Creating the foundation chart
Once you have connected the dataset to Tableau, you should see the three 3 columns as shown above.
Place Quarter in columns and. measure values pill in rows.
Convert quarter to a measure value and change it from “YEAR” to “QUARTER”. Make sure you change the Net Income and Revenue pills in Measure Values to SUM.
Step 2: Creating the Parameter
We need a parameter to store the user input and use it in our calculated fields later on. Create a percentage of sales parameter to take in the user input.
Step 3: Creating % of Revenue calculated field
Create a new calculated field as shown above. Make sure you follow the exact name labeled for your parameter. It should be highlighted in purple when you input it correctly in your calculations. Replace the “Revenue (US $M)” field with your “% of Revenue” pill. Right-click on the “Percentage of Sales” parameter and click “Show Parameter”. If done correctly, you should achieve the output as shown below.
Step 4: Dual Axis Charts
Congrats! You are halfway there. Next, to ensure Tableau tracks when profits surpass partial sales, I have to create the IF formula as shown below.
Next drag Measure Names to colors (Control + Left Click for Window Users/ CMD + Left Click for Mac users) and you should get a color differentiating the Profit and Partial Sales line graphs.
Drag “Netincome>% Revenue” to rows and click on Dual Axis.
You should get a similar output as shown above. Next, follow the series of steps shown in the GIF below.
You should achieve a beautiful Dual Axis Charts by the end. With Stars indicating when Net Profit exceeds sales.
Step 5: Window Calculations
Reaching there!
However, I am only indicating to Tableau to mark points when Profits exceeds partial sales. I did not specify HOW should Tableau count such occurrences and over WHAT period of time. I will need the additional formula as shown below.
The Window_count formula above indicates that throughout the full period of the dataset, count the number of times Net Income exceeds partial revenues.
Drag the “Count NetIncome>%Revenue” pill to detail in All and click on Edit Title. To ensure the Title is dynamic, insert the Parameter value and Count value shown below.
Change the title to your own preference and click on ‘OK’.
Final Output:
Congratulations on completing this tutorial! I will leave it to your creativity to format and decorate the charts. If you want some reference to my workbook, the link is right below.
Link:
Do leave a clap and share it with your friends if you find it helpful. If you have any feedback, please reach out to me on LinkedIn. Any form of comment or feedback will be appreciated. Thank you!
