After listening to Jacob Olsufka’s session at Tableau Conference I decided to create a KPI style dashboard using this month’s #SportsVizSunday data. The viz can be found here, and I have placed a screen shot below. In this blog I will go through some of the calculations I used to enable the set actions and parameter actions interactivity.

KPI Metrics.

The high level KPIs at the top of the dashboard are all driven using the year parameter in the bar chart showing the line trend to the right. The title of each section are worksheets with the year parameter as well. See below how to embed that year parameter into your KPI calc.

1. Main Metric – Tableau Bold, 18 font, #333333
   • SUM(IF [Year]=[Year Parameter] then [Total Revenues] END)
2. YoY Change – Tableau Semi-bold, 11 font, #555555
   • (SUM(IF [Year]=[Year Parameter] then [Total Revenues] END) -SUM(IF [Year]=([Year Parameter]-1) then [Total Revenues] END)) /SUM(IF [Year]=([Year Parameter]-1) then [Total Revenues] END)
3. Change Since [Year] – Tableau Semi-bold, 11 font, #555555
   • (SUM(IF [Year]=[Year Parameter] then [Total Revenues] END)-SUM(IF [Year]=[School | Min Year] then [Total Revenues] END))/SUM(IF [Year]=[School | Min Year] then [Total Revenues] END)
   • [School | Min Year] : IF [Conference Set | SA] THEN {FIXED [FBS Conference]:MIN([Year])} ELSE {FIXED [FBS Conference],[School]:MIN([Year])} END
     • This calc is necessary because when you drill down from Conference into School, the LOD you’re using will need to adjust for the level at which you’re aggregating.
4. Up and Down Arrows
   • if [Expenses | 2017 since 2005]>0 THEN “▲” END / if [Expenses | 2017 since 2005]<0 THEN “▼” END
   • Put these in your text marks card and make them separate colors (red & green in this instance) and the one that is NULL will not show in your final rendering.

Set Action Drill Down.

If you click on the arrows showing the conferences you are able to drill down and just see the KPIs for the schools in that conference. Here is now to create such a drill down:

1. Create a Set from the field that you want to drill into, in this care it’s Conference which I call [Conference Set] created from the [FBS Conference Labels] dimension
2. Create a field to be in the most left hand column. Taking the trick from Andy Kriebel’s YouTube video on Set Actions, I used the right arrow with the Conference Abbreviation field to create this field.
   1. IF [Conference Set] THEN “▼ ” ELSE “► ” END + [Conf Abb]
3. Add the dimension that you created the [Conference Set] from as the second blue pill in rows, in this case it was [FBS Conference Labels]
4. Create a field showing what will appear when a Conference is included in our set. In this case I want the School to show when the Conference is IN the set, else “”. Add this pill to rows, it will be the third blue pill
   1. IF [Conference Set] THEN [School Abb] ELSE “” END
5. Right click and hide the second column, [FBS Conference Labels]
6. Create a second Set from the dimension [FBS Conference Labels], this time selecting all of the conferences. Drag this set to Filters.
7. Add Set Action with your target set being the [Conference Set] you created in step 1. Do this on Click and then Remove all values from set when clicking off.
   1. When you select the Conference the value is included in the [Conference Set], and now the “► ” END + [Conf Abb] will show in the first blue pill
   2. The third blue pill will now show the [School Abb] field per the cal in step 4.
8. Add Set Action with the target set being the second set you created. Do this on Click and then Add all values to set when clicking off.
   1. The concept here is that when you click on a conference no values will be in the set (and it’ll be in the first set), but when you click off of it, it will add back all the conferences to the set.

Parameter Action.

As discussed in the KPI section of this blog, all the calcs in this dashboard are driven from the year parameter. By clicking on the year bars you are choosing what totals to show in the bigger bold numbers, the year to compare YoY values for, and the year for to compare the historical change to. This is done through a parameter action. Here is how to create that parameter action

1. Create parameter for the field that you want to drive your calculations. In my case I use the Year field.
2. Create calculations that you want to be controlled via the year parameter. The calculation below shows the YoY calc for revenues. The parameter takes place of the year we’re analyzing, then the parameter-1 is the year before.
   1. (SUM(IF [Year]=[Year Parameter] then [Total Revenues] END) -SUM(IF [Year]=([Year Parameter]-1) then [Total Revenues] END)) /SUM(IF [Year]=([Year Parameter]-1) then [Total Revenues] END)
3. Bring the field you want to be toggled in your dashboard somehow. In my case I brought it in via the bar charts at the top. It could be in various different forms, like a panel of different values you can click on.
4. Create a Parameter Action in your dashboard actions. Your target parameter is the year parameter crated in step 1. The field that should be controlled by this parameter is [Year] in this example. Choose Click for what to run the action on.

This parameter action also controls the color of the bar chart. I used a dual axis to have a blue bar chart over the current bar chart with the formula below. I used the same concept to place the blue dot on the sparklines.

SUM(IF [Year]=[Year Parameter] THEN [Total Revenues] END)

Thanks so much for reading! If you have any questions, comments, or feedback for me on my viz or on this blog, please feel free to email me at spencer.baucke@tessellationconsulting.com.