Table calculations are a powerful feature within Tableau that let you perform computations directly on the data already present in your visualization. These calculations are dependent upon the structure of your table and the dimensions in your view.
Addressing vs. Partitioning:
Addressing defines the set of cells a table calculation is computed along (e.g., across a row, down a column).
Partitioning divides the table into sub-groups, and the calculation is performed separately within each partition.
Scope (Direction): Determines if the calculation is done 'across' (horizontally) or 'down' (vertically) your table.
Nested Table Calculations:
You can combine multiple table calculations to create more advanced logic.
Cheat Sheet: Complex Tableau Table Calculation
Important Notes:
You can configure most table calculations by right-clicking the calculation in the view and selecting "Edit Table Calculation...".
Table calculations are affected by the dimensions in your visualization.
It's often useful to use table calculations in conjunction with regular calculated fields.
Let's say you're analyzing sales data for a company:
RUNNING_SUM(SUM([Sales])) would visualize how revenue accumulates throughout the year.
WINDOW_AVG(SUM([Sales]), -11, 11) would calculate a 12-month moving average of sales to smooth out seasonality.
LOOKUP(SUM([Sales]), -1) would allow you to compare the current month's sales to the previous month's sales.
RANK(SUM([Sales])) would rank your product lines by total sales volume.
RUNNING_SUM()
Calculates the cumulative sum across the table.
Problem: A retail company wants to track how its total sales revenue accumulates throughout the year. Solution: RUNNING_SUM([Sales]) visualizes revenue growth and helps identify peak sales periods.
RUNNING_AVG()
Calculates the running average across the table.
Problem: A stock analyst needs to smooth out daily fluctuations in stock prices to identify underlying trends. Solution: RUNNING_AVG([Stock Price]) reveals long-term price movements.
RUNNING_COUNT()
Calculates a running count of the records in the table.
Problem: A marketing team wants to monitor how their customer base grows over time. Solution: RUNNING_COUNT([Number of Customers]) charts customer acquisition progress.
RUNNING_MIN()
Calculates the running minimum value.
Problem: An investor wants to track the lowest historical price of a stock to inform buy decisions. Solution: RUNNING_MIN([Stock Price]) helps identify potential buying points.
RUNNING_MAX()
Calculates the running maximum value.
Problem: A sales manager wants to identify peak sales performance periods to analyze successful strategies. Solution: RUNNING_MAX([Sales]) highlights periods for further investigation.
WINDOW_SUM()
Sums values within a specified window (a range of rows).
Problem: A finance team needs to analyze quarterly sales trends. Solution: WINDOW_SUM(SUM([Sales]), -2, 0) calculates quarterly rolling sales totals.
WINDOW_AVG()
Averages values within a specified window.
Problem: A website manager needs to understand average website traffic over 7-day periods. Solution: WINDOW_AVG(SUM([Traffic]), -3, 3) calculates the 7-day moving average of traffic.
INDEX()
Assigns a sequential index to each row in the table.
Problem: A sales team needs to rank their top-performing products within each region. Solution: INDEX() used with partitioning creates region-specific rankings.
FIRST()
Returns the offset from the first row in the partition.
Problem: A customer analytics team wants to identify the date of each customer's first purchase. Solution: FIRST() finds the earliest [Order Date] per customer.
LAST()
Returns the offset from the last row in the partition.
Problem: A project manager needs to determine the most recent task completion date within a project phase. Solution: LAST() finds the latest [Completion Date] within each phase.
LOOKUP()
Retrieves values from a different position in the table.
Problem: A sales analyst wants to compare this month's sales to the sales from the same month last year. Solution: LOOKUP(SUM([Sales]), -12) fetches last year's corresponding sales value.
PREVIOUS_VALUE()
Returns values from the previous row.
Problem: A finance team needs to calculate the percentage change in sales from month to month. Solution: (SUM([Sales]) - PREVIOUS_VALUE(SUM([Sales])))/PREVIOUS_VALUE(SUM([Sales]) ) provides the growth rate.
RANK()
Ranks records within a partition.Problem: A sales manager wants to identify the top-performing salespeople across the entire company. Solution: RANK([Sales]) ranks salespeople for the entire dataset.
Learn how Chromadata is helping organizations streamline and consolidate complex data flows to reveal actionable business intelligence, leading to immediately usable insights. For questions, email info@chromadata.com