Tableau Formulas I Use Daily as a Data Analyst (Cheat Sheet Included!)
As a data analyst, I’ve found Tableau to be one of the most powerful tools for visual analytics—but its real magic comes from calculated fields. Whether you're cleaning data, creating KPIs, or building interactive dashboards, knowing the right formulas is key.
Here are the most essential Tableau formulas I use every day, with real-world examples and pro tips.
1. Basic Aggregations (SUM, AVG, COUNT, etc.)
When to use: Quick totals, averages, or counts in visualizations.
- Example:tableauCopySUM([Sales]) // Total revenue AVG([Profit]) // Average profit margin COUNTD([Customer ID]) // Distinct customer count
- Pro Tip: Use
COUNTD()instead ofCOUNT()for unique values.
2. IF / THEN / ELSE – Conditional Logic
When to use: Segmenting data, flagging outliers, or creating bins.
- Example: Categorize customers as “High Value” if sales > $1000:tableauCopyIF [Sales] > 1000 THEN “High Value” ELSE “Standard” END
- Advanced Use: Nest with
AND/ORfor complex rules.
3. CASE WHEN – Cleaner Than Nested IFs
When to use: Multi-condition categorization (like Excel’s SWITCH).
- Example: Tier customers based on sales:tableauCopyCASE [Sales] WHEN > 5000 THEN “Gold” WHEN > 1000 THEN “Silver” ELSE “Bronze” END
- Why it’s better: More readable than
IF(IF(IF())).
4. DATEPART / DATENAME – Extracting Date Components
When to use: Breaking down dates (e.g., year, quarter, weekday).
- Example:tableauCopyDATEPART(‘year', [Order Date]) // Returns 2024 DATENAME(‘month', [Order Date]) // Returns “January”
- Pro Tip: Use this for time-based drill-downs.
5. DATEDIFF – Calculating Time Intervals
When to use: Measuring durations (e.g., days to delivery).
- Example: Days between order and shipment:tableauCopyDATEDIFF(‘day', [Order Date], [Ship Date])
- Bonus: Combine with
TODAY()for aging reports.
6. ZN / ISNULL – Handling Nulls Gracefully
When to use: Replacing nulls with zeros or default values.
- Example:tableauCopyZN([Profit]) // Converts null to 0 IF ISNULL([Discount]) THEN 0 ELSE [Discount] END
- Why it matters: Avoids skewed averages and broken visuals.
7. LOD Expressions (FIXED, INCLUDE, EXCLUDE)
When to use: Complex aggregations at different granularities.
- Example: Calculate average sales per customer (ignoring viz filters):tableauCopy{ FIXED [Customer ID] : AVG([Sales]) }
- Pro Tip:
INCLUDE/EXCLUDEadjusts for dimensions in the view.
8. WINDOW_AVG / WINDOW_SUM – Moving Calculations
When to use: Rolling averages, running totals.
- Example: 7-day rolling average:tableauCopyWINDOW_AVG(SUM([Sales]), -6, 0)
- Bonus: Combine with
INDEX()for dynamic ranges.
9. REGEXP – Pattern Matching
When to use: Extracting or validating text patterns.
- Example: Check if email is valid:tableauCopyREGEXP_MATCH([Email], ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
- Note: Requires Tableau Prep or custom SQL in some versions.
10. PARSE – Splitting Strings
When to use: Extracting parts of text (e.g., domain from email).
- Example: Get domain from email:tableauCopySPLIT([Email], ‘@', 2)
- Alternative:
REGEXP_EXTRACT([Email], '@(.*)')
Bonus: Quick Reference Cheat Sheet
| Formula | Use Case | Example |
|---|---|---|
SUM([Sales]) | Total revenue | SUM([Revenue]) |
IF [X] THEN Y END | Conditional logic | IF [Profit] > 0 THEN "Yes" END |
{FIXED X : AVG(Y)} | Level-of-detail calc | {FIXED [Region] : SUM([Sales])} |
DATEDIFF('day', A, B) | Days between dates | DATEDIFF('day', [Start], [End]) |
ZN([Profit]) | Replace null with zero | ZN([Discount]) |
Final Thoughts
Tableau’s real power comes from calculated fields—whether you're building KPIs, cleaning data, or creating interactive dashboards. Start with basic aggregations and conditional logic, then level up to LODs and window functions.
What’s your most-used Tableau formula? Let me know in the comments!
Resources & Learning
- Tableau’s Official Calculation Guide
- Andy Kriebel’s Tableau Tips (practical examples)
- Tableau Public Gallery (for inspiration)
Want a deep dive into LOD expressions? Let me know—I’ll break them down with real-world examples! 📊