Tableau Calculation Types
Calculations allow tableau to add clean data, add columns of data, etc
Calculations are entered in “Calculated Fields” which are essentially the same as a “Cell” in Excel.
A good question to ask youself before writing the calculation:
If you need a new number on every row you would use a Role Level Calculation
If you need a single number at the bottom in excel, you would use a Aggregate calculation
Table calculations only operate on data in you visualization and only how it is organized in your visualization.
Once you add a calculation field you can use it the same way you use measures and dimensions.
How to Write Calculations
- Write click below measures and select “Create Calculated Field”
- Enter Title: as descriptive as you can
- Enter the calculation in the blank box below the title.
- Drag variables into the formula from the dimensions/measures toolbar.
- The variables must match the type of formula (string–string) (integer–Integer)
- All words need to be in quotes
- If you add // you can add a comment in your calculation field
- If the formula is Bold Blue than it is on a aggregate function
If there is an equal sign next to the measure/dimension it is a calculation
Calculations that make filtering more efficient
Write a calculation that shows True = State you would like to live in, False= State you don’t want to live in.
Examples of IF and CASE formulas.2 3 9
Identifying companies that pay less than the prevailing wage
Since there is no variable for this calculation we can create it!
- Simply create a formula of paid wage-prevailing wage
- De-aggregate the graph to see the scatterplot
- We can see there are some values that are below zero indicating that some companies are paying below prevailing wage.
2. To view ONLY the points below prevailing wage we can create another calculation:
IF [Difference between prevail and paid] <0
THEN ‘Paid less than prevailing wage’
ELSE ‘Paid prevailing wage or higher’
3. Now, if we want to see which companies are to blame we can use a previous chart.
- First to only look at data related jobs lest put ‘Job title subgroup’ in the filter and filter out all other jobs.
- Re-aggregate the measures
- Instead of median look at count
- Pull Employer name on “Size” on marks card
- Change “Show me” to tree chart (rare opportunity to use it)
- Since there are a ton of companies with only 1, they may be flukes. Let’s create a filter by pulling “Difference between …” to filter card, selecting “count” and making the minimum value 5.
Blending price Parity Data with Our Salary Data
Tableau can blend data put two sources of data together when they have two different sources of aggregation. In order for tableau to blend, there needs to be one field that is common to both data sets. In our example, state name.
- The sheet with the lower level of granularity should be the primary doc (in example we used doc with more columns as primary
- These variables will always be measures
Adjusting data-related Salaries for Cost of living
- Open the lower level data set (locating parity)
- Data menu –> Edit relationships
- If the column headers do not match use “Custom” to make sure the headers are the same.
Calculating which States have top Adjusted Salaries per Job Category
Using Table Calculations
When you blend data sources, the new calculated field is dependent on the blended variable. For example, in our salary sheet, the “Adjusted Salary” variable will not bring up any values unless the “Work State” variable is also included.
When you try to sort with multiple categories, it does not sort within each category but in some way across the whole table.
- Tableau doesn’t have a way to sort based on what is in the graph. Using a table calculation we can have the table sort by rank of adjusted wage by subgroup.
- We need to duplicate our calculated pill by holding down Ctrl key and dragging pill to the “detail” in the marks card.
- Right click on pill in marks card and click “add table calculation”
- For this example, use Rank
- Define “Running Along” – Bold options will have the calculation be based on what the graph looks like RIGHT now, advice is to NEVER use these options. Use the “Advanced” options and select what category you want to order.
- Partition: What you are going to use to group (job title subgroup)
- Address: What you are going to apply it to. (Work State)
- Leave sort as automatic
- Choose “Descending” since we want highest value first
- Once the calculation is complete we need to apply it to our graph!
How to apply the new table calculation to the graph
- Since we want the calculation to act on each subgroup, then reset and start over when it reaches the next subgroup, we need the variable to be discrete and not continuous
- Right click on the pill on the marks card and change the variable to “Discrete”
- Furthermore, since we want our calculation to work “below” job title subgroup but work on “work state” we want to drag the pull in between the two pills on the columns tab.
3. We can remove the 1.2.3 header by right clicking on one of the numbers and unchecking “show header”
4. We also want to remove the guidelines by right clicking on the graph, click format, click on the outline icon, navigate to “column divider” and drag level marker down a notch.
Using Parameters To Define Top States
Parameters are placeholders for anything, string, number, date, and are interactive. Anyone viewing can change them very quickly
There are different than filters because they are global, they are applied to all worksheets. You can also use them in calculations.
Our example: use parameter as an alternative to table rank calculation.2 3 9
Two steps for our strategy:
- Make the parameter
- Make a calculation that compares the rank in each item to the parameter
- Put the calculation on the filter shelf.
How to make a parameter:
- Right click on variable pane or analysis drop down
- Click Create Parameter and name it
- Select the data type for what the value will be (our example integer)
- Type the value you want it to start at, default and click OK
We now have the parameter created. In order for it to act on anything we need to make a calculation that included the parameter in the equation.
In our example we want to create an equation that calculates (true or false) whether or not a value in our rank matches our parameter. In other words, to only show the rank values that satisfy the parameter. Since items are ranked 1-8 we can use the number or rank to sort our values.
- Right click on variable pane and select “Create Calculated Field”
- Drag our “Rank” pill from the columns tab <= Parameters pill
- RANK([Adjusted Paid Wage])<=[Rank x]
- Drag the newly created calculation pill the marks card to be edited.
- Click the arrow on the pill and select “edit table level calculation”
- Select compute using: “advanced”
- Match appropriate partitioning and addressing categories
- Drag pill from the marks card to the filters tab, and select “true”
- Right click on the parameter pill or open drop down and select “Show parameter control”
- You can now edit how the parameter impacts the table.
Lesson 5: Calculating Which Companies Have the Top adjusted Salaries with Job Subcategories
Dashboards! Give your customer exactly what they want, how they want it!
How to look at multiple graphs at once, in one place. You also use the results in one graph to influence another. Dashboards allow team members to make notes and annotations
How to make a dashboard:
- Click on icon on bottom right “New Dashboard”
- Change view options to laptop
- Use the “Containers” (horizontal, vertical) to structure your dashboard
- Add actions via the “Dashboard” –> Actions option
- You can add filter or highlight items based on what you select
- You should also remove any superflous legends or graphs to make the dashboard better looking.
Dashboards are super helpful and can be fun to navigate. However you never know what people are looking at and if they are following your narrative. That’s where stories come into play!
Visual Story Points in Tableau
Story points are a sequence of worksheets that you design. Each worksheet can have anything on it! You can arrange them in a sequence so you can create your narrative.
Overview on how you get started: