All in Table Calculations
In many Tableau Table Calculations, you can use the default Scope and Direction options to execute the calculation the way you want. However, that isn't always the case, especially if you have three or more dimensions in your worksheet.
If you're having trouble getting a Table Calculation to execute the way you want, Specific Dimensions is your lifeline! This feature allows you to have exact control over how the calculation executes.
The shorthand rules for Specific Dimensions are...
When should you use Table Calculations and when should you use an LOD? There are differing opinions out there...
When the calculation is dependent on information in your window, use a Table Calc. When it's independent, use an LOD.
Unless... your workbook starts to show performance issues.
FIXED Level of Detail Functions (LODs) let you specify the level of detail you want to aggregate a particular measure at. This allows us to work around the natural limitations of the level of detail in the worksheet we're working in.
FIXED LODs are a great place to start as they are the definitive, essential LOD. The two others (EXCLUDE and INCLUDE) are less common, but functional in their own unique ways. If you want to simplify and just learn one LOD type, FIXED is the one for you!
Have you ever been asked to include a table of data that’s way too detailed for the dashboard you’re working on? Sometimes we really just want to see all the numbers. But we might not necessarily want to display all that information at once.
That’s where a Show/Hide option could be effective. Using a parameter, we can enable our end-users to select whether they want to see all the data in the underlying table, or just some summary data (like some total columns).
One of the biggest problems with scatter plots is that all the data points on the chart can be overwhelming without context.
Scatter plots are one of the most powerful visuals you can build in Tableau. They allow you to plot numerous data points to identify correlation and patterns in your data set. However, they can also be overwhelming for an end user.
Using resources like average lines and colors to identify data points with "desirable" versus "undesirable" outcomes can expedite your users' ability to properly comprehend the data.
Tableau WINDOW functions allow you to apply an aggregation to aggregated data points in a worksheet.
This is helpful when you want to calculate the average, minimum or maximum of your data points! These functions can help you draw average lines, differentiate between values that are above or below average, or even highlight the largest and smallest values in your visuals!
WINDOW functions are a core component of maximizing Tableau's ability to communicate valuable insights about your data.
How do you track trends in Tableau when there is a high degree of fluctuation in our data? Try using a moving average!
A moving average is an average of a collection of points around a specified point. For each mark in our view, Tableau will calculate the average of the mark's value and the value of several previous marks. This creates an average value that "moves" over time.
If you want to reference data that isn't visible in your Tableau worksheet, you're going to need a trick to 'hide' that data rather than filter it out of the worksheet entirely. For example, if you want to calculate year over year growth but only display the latest year, you can't filter the prior year out of your worksheet or Tableau will not be able to reference the prior year values.
Hiding data with a filter in Tableau is an expert trick you should know about. One of the best ways to 'hide' data with a filter is to use a Table Calculation filter. Table Calculations filters are one of the last steps processed in Tableau's order of operations. They are even processed after your standard table calculations (e.g. Percent of Total, Rank, Percent Difference) are computed.
Sometimes we want to calculate the percent of total of a variable, but those totals add up to over 100%! What's going on?
In those cases, the dimensions we use in the view likely have overlapping members. For instance, a single customer could have purchased at all our stores, meaning that the percent of total for our number of customers is skewed.
Imagine you are building a line graph and want to visually call out the highest and lowest values for your end user. To do that, we can create a dual axis chart where circles representing the MIN and MAX points is overlaid on the line graph.
How can we calculate the highest and lowest values in a Tableau view? These values need to be dynamic (as we filter out info, the values need to change), and they need to be integrated into what we’ve already built. But how can we do that?
Do you want to use Tableau to look at running headcount over time? For example, you work with employee level data which has Start Dates and End Dates and you'd like to be able to see how the total active headcount at the company has changed over the last 8 quarters.
That's something Tableau can do, but it isn't going to work out of the box. It's going to take some creative data structuring and calculations to get things working.
Sometimes, you have too many values in a dimension on the rows shelf in your Tableau worksheet and end up with a scroll bar. Annoying, right? Scroll bars mean your users are less likely to see the data at the bottom of your worksheet because it it out of sight and out of mind.
One idea I've heard discussed is, what if you could break the data into multiple columns? For example, instead of displaying 50 states as a single column of 50 rows, could you display it at 2 columns or 25 rows?
Table Calculations are a family of functions in Tableau Desktop which allow users to reference data at the aggregated, data point (mark) level. They are a powerful but often misunderstood component of the Tableau ecosphere.
Some of the most common applications of Table Calculations include calculating Percent of Total, Percent Difference and Rank.
