Tableau calculations transform raw data into insights. Master dimensions (categorical), measures (quantitative), LOD expressions (aggregation control), and table calculations for professional analytics.
Dimensions vs Measures: Foundation Concepts
Dimensions (Discrete/Blue): Categorical fields: names, dates, regions. Create row/column headers. Filter data. Generate multiple marks (one per dimension value). Used in GROUP BY (SQL). Dimensions break data by category—essential for exploration.
Measures (Continuous/Green): Quantitative fields: sales, revenue, count. Aggregated (SUM, AVG, COUNT). Create axes. Encoded via position/color/size. Numeric data for calculations. Measures answer “how much”—quantify performance.
Field Types Impact Behavior: Blue (discrete) dimension [Region]: creates separate columns/rows per region. Green (continuous) measure [Sales]: creates continuous axis. Converting field type: right-click field → Convert to Discrete/Continuous. Impact: worksheet structure changes completely (detail vs aggregation).
Calculated Fields: Creating New Dimensions/Measures: Formula-based fields: IF [Profit] > 0 THEN “Profitable” ELSE “Loss”. Scope: row-level (default, calculated per record) vs aggregate (SUM, AVG post-aggregation). Type determines usage: dimension calculation (result used as dimension) vs measure calculation (aggregated). Syntax errors caught at creation—instant feedback.
LOD (Level of Detail) Expressions: Control aggregation scope. FIXED: aggregate at specific level (ignore filters). INCLUDE: add dimensions beyond current scope. EXCLUDE: remove dimensions from scope. Syntax: {FIXED [Region] : SUM([Sales])}. Use: global calculations, year-over-year, market share (region sales / total sales). LOD essential for complex analysis beyond simple aggregation.
Table Calculations: Row-Level Context: Calculate within partition/window of data. RUNNING_SUM([Sales]): cumulative total. RANK([Sales]): ranking by dimension. PERCENT_OF_TOTAL: contribution %. INDEX: row position. Require partitioning (dimensions to compute within). Used after aggregation (unlike calculated fields).
5 Complete Solved Interview Problems
Question 1: Segment Customers by Profitability
Create dimension: Profitable if Profit > 0, Loss if Profit <= 0. Use in visualization. Profit by segment?
IF [Profit] > 0 THEN “Profitable” ELSEIF [Profit] = 0 THEN “Break-Even” ELSE “Loss” END
Drag to Rows. Drag Profit (measure) to Values. Result: bar chart showing profit by segment.
Explanation: IF/ELSEIF/ELSE creates dimension values. Result: blue field used for grouping. Visualization shows three bars (Profitable, Break-Even, Loss) with sum profit. Essential for segmentation analysis.
Google – Senior Analytics Manager
Question 2: Calculate Market Share
Show each region’s sales as % of total. Use LOD. Handle filter context?
{FIXED : SUM([Sales])} / SUM([Sales]) * 100
Results: FIXED calculates total across all (ignores filters). Divide by current (respect filters). Market share recalculates per selection.
Explanation: FIXED {}: aggregate at specific level regardless of filter. Essential for global baseline. Market share = regional / global. Filter on region → market share still shows % of that filtered total.
Meta – BI Engineer
Question 3: Year-Over-Year Growth
Compare current year sales vs prior year. Calculate % growth. Use LOD + table calculation?
{FIXED [Year]: SUM([Sales])} WHERE [Year] = YEAR(TODAY())
Calculated Field: Prior Year Sales
{FIXED [Year]: SUM([Sales])} WHERE [Year] = YEAR(TODAY())-1
Calculated Field: YoY Growth %
(Current Sales – Prior Year Sales) / Prior Year Sales * 100
Explanation: LOD FIXED isolates year calculation. WHERE filters specific year. YoY growth shows % change. Visualization: line chart with growth rate. Handles filter context correctly.
Amazon – Analytics Engineer
Question 4: Ranking & Top-N Analysis
Rank products by sales (descending). Show top 10 products. Use table calculation?
RANK(SUM([Sales]))
Drag Product, Sales to worksheet. Add Rank to filter. Filter: Rank <= 10. Result: top 10 products only.
Explanation: RANK() table calculation ranks rows. Partition by product (implicit). Filter rank <= 10 shows top 10. Sorting descending ensures accurate ranking. Handles ties (same rank for equal sales).
Apple – Analytics Engineer
Question 5: Running Total & Cumulative Analysis
Show monthly sales with running total. Cumulative % to target?
RUNNING_SUM(SUM([Sales]))
Calculated Field: Cumulative %
RUNNING_SUM(SUM([Sales])) / SUM({FIXED: SUM([Sales])}) * 100
Drag Month to Columns. Sales, Running Total, Cumulative % to Rows (multiple measures).
Explanation: RUNNING_SUM() accumulates within partition. Partition by month (implicit). Cumulative % = running total / annual total. Visualization: line chart showing sales trend + cumulative growth. Essential for sales pipeline analysis.
Microsoft – Analytics Engineer
15 Practice Questions
Q6: Convert Sales field from Measure to Dimension. Impact on worksheet?
Q7: Create calculated field: discount rate = discount / list price. Data type? Dimension or measure?
Q8: LOD INCLUDE: add product dimension to region analysis. Syntax?
Q9: LOD EXCLUDE: remove month from year-to-date. Use case?
Q10: Table calculation WINDOW_SUM: 3-month moving average. Implementation?
Q11: Difference from prior month: PREVIOUS_VALUE() table calculation. Syntax?
Q12: Percentile calculation: find 90th percentile sales. Use PERCENTILE()?
Q13: Index() vs RANK(): difference when sales equal?
Q14: Nested IF: category → subcategory → product. Complex segmentation?
Q15: String manipulation: concatenate first + last name. STR() function?
Q16: Date calculations: days since purchase. DATEDIFF()?
Q17: Case statement vs IF: readability, performance?
Q18: Create measure: profit margin %. Handles zero denominator?
Q19: Conditional aggregation: SUM only if profit > 0. SUMIF()?
Q20: LOD with EXCLUDE: total sales minus current product. Syntax?
Master Tableau Calculations
Get our comprehensive Data Analytics: Ace All The Interview Concepts course.