How to Calculate Ratios in Tableau

Calculating ratios is a fundamental way to find new meaning in your data. Instead of just looking at raw numbers like total sales or website users, ratios give you powerful context by comparing one metric to another - revealing efficiency, profitability, and performance. This article will walk you through three different methods for calculating ratios in Tableau, from simple formulas to more advanced techniques.

Why Ratios Are So Important for Data Analysis

Numbers in isolation can be misleading. Knowing you made $100,000 in sales sounds great, but it's more meaningful when you know it cost you $80,000 to make those sales. The relationship between those two numbers - your 20% profit margin - tells a much richer story. Ratios turn raw data into actionable insights.

Here are a few common examples that businesses track constantly:

• Profit Margin: Tells you how profitable your sales are. (Profit / Sales)
• Conversion Rate: Shows you how effectively you turn visitors into customers. (Conversions / Visitors)
• Return on Ad Spend (ROAS): Measures the effectiveness of your advertising campaigns. (Revenue from Ads / Ad Spend)

Tableau makes it easy to calculate these and any other custom ratios you need. Let's look at the primary ways to do it.

Method 1: Using Basic Calculated Fields

The most straightforward way to calculate a ratio in Tableau is by creating a calculated field. This method works perfectly when you need to perform a simple mathematical operation on two or more fields from your data source.

For this example, we’ll calculate a Profit Ratio using the Sample-Superstore dataset that comes with Tableau. The formula is simply Profit / Sales.

Step-by-Step Instructions:

1. Connect to Your Data: Open Tableau and connect to the Sample - Superstore data source.
2. Create a New Calculated Field: In the Data pane on the left, right-click any empty space and select Create Calculated Field. This will open the calculation editor.
3. Name Your Calculation: Give your new field a descriptive name. Let's call it "Profit Ratio."
4. Enter the Formula: In the formula box, type the following expression:

SUM([Profit]) / SUM([Sales])

A Quick Note on Aggregation with SUM()

You might wonder why we use SUM([Profit]) / SUM([Sales]) instead of just [Profit] / [Sales]. The difference is about aggregation.

• [Profit] / [Sales] would calculate the profit ratio for every single row of your data first, and then it would aggregate those ratios when you add them to a chart (for example, by averaging them).
• SUM([Profit]) / SUM([Sales]) adds up all the profit for a given category (e.g., all profit from the "Technology" category) and divides it by the total sales for that same category. This is almost always what you want for a ratio, as it gives you the true performance of the overall group.

1. Format the New Field: After creating the field, find "Profit Ratio" in your Data pane (it will be under Measures). Right-click on it, select Default Properties > Number Format..., and choose Percentage. Set the decimal places to one or two for a clean look.
2. Use Your New Ratio in a View: Now, you can drag and drop your new "Profit Ratio" field into a visualization. For example, drag the Category dimension to the Rows shelf and your new Profit Ratio measure to the Columns shelf. You'll instantly see a bar chart comparing the profitability of each category.

Method 2: Using a Quick Table Calculation

Sometimes you need a quick ratio without creating a permanent calculated field. Table calculations are perfect for this. They perform computations on the data that is currently in your view, making them fast and flexible for on-the-fly analysis.

Let's find the Percent of Total Sales for each product sub-category. This will tell us what portion of our total revenue each sub-category is responsible for.

Step-by-Step Instructions:

1. Create a Simple View: Start by dragging the Sub-Category dimension to the Rows shelf and the Sales measure to the Columns shelf. You'll now have a bar chart showing total sales for each sub-category.
2. Add the Sales Field Again: To see both the dollar amount and the percentage, drag the Sales measure from the data pane to the Label mark on the Marks card. Now you have the sales values labeled on each bar.
3. Apply the Table Calculation: Drag the Sales measure to the Label mark on the Marks card one more time. Now you should have two labels for SUM(Sales). Right-click the second one, mouse over Quick Table Calculation, and select Percent of Total.

Instantly, Tableau recalculates that measure to show its percentage contribution to the overall total sales in the view. It’s an incredibly fast way to contextualize your data.

You can also control how the calculation is computed. By right-clicking the table calculation pill and selecting Compute Using, you can change the scope of the calculation (e.g., Table (across), Pane (down), etc.).

Method 3: Level of Detail (LOD) Expressions

What if you want to calculate a ratio that compares a slice of your data against a much broader total? For example, you want to see how a specific sub-category's sales compare to the entire company's total sales, regardless of how you drill down or filter your view?

This is where Level of Detail (LOD) expressions come in. They allow you to compute values at a different level of granularity than what's currently shown in your visualization.

Let's use an LOD Expression to calculate the Percent of Total Sales. This time, our calculation will work even if we add more dimensions or filters to our view.

The Problem without an LOD:

If you used a simple calculated field like SUM([Sales]) without an LOD, context would change with your view. For a 'Phones' sub-category bar, SUM([Sales]) would return the sales for Phones. The denominator would also be sales for Phones. That gives you 100%, which isn't what you want. We need the denominator to be the sum of sales for everything.

Building the LOD Ratio:

Here's the formula we’ll use. It looks a bit complex initially, but it’s easy once you break it down.

SUM([Sales]) / SUM({FIXED : SUM([Sales])})

Breaking Down the Formula:

• SUM([Sales]): This is our numerator. It's the standard aggregation that calculates sales at the level of detail present in our view (e.g., for each Sub-Category bar).
• {FIXED : SUM([Sales])}: This is the denominator and the magic part. The FIXED LOD tells Tableau to calculate the total sum of sales for the entire dataset, completely ignoring any dimensions in the view like Sub-Category, Region, or Date. It locks the calculation to that specific level - in this case, the grand total.

By dividing the first part by the second, we get a true percent-of-total ratio that remains constant and accurate no matter how our visualization changes.

Step-by-Step Instructions:

1. Create a New Calculated Field: Right-click in the data pane, select Create Calculated Field.
2. Name It and Enter the Formula: Name your field "Percent of Total Sales (LOD)" and paste in the formula from above.
3. Format and Visualize: Just like before, format this new measure as a percentage. Then, drag Sub-Category to Rows and "Percent of Total Sales (LOD)" to Columns. The result will look identical to the table calculation result, but it is far more robust and can be used in tooltips and other calculations more easily.

Bonus: A Cookbook of Common Ratios in Tableau

Now that you know the methods, here are the formulas for some common business ratios you can build right away.

Marketing Ratios

• Conversion Rate (for e-commerce): Finds the percentage of sessions that result in a purchase.

COUNTD([Order ID]) / COUNTD([Session ID])

• Customer Acquisition Cost (CAC): The cost to acquire a new customer.

SUM([Marketing Spend]) / COUNTD([New Customer ID])

Sales & Finance Ratios

• Profit Margin: The classic measure of profitability.

SUM([Profit]) / SUM([Sales])

• Average Deal Size: The average value of a closed deal.

SUM([Won Deal Value]) / COUNTD([Won Deal ID])

(Note: Fields will vary based on your CRM data structure).

Final Thoughts

Calculating ratios turns simple reporting into genuine analysis, allowing you to quickly spot what's working and what isn't. Whether you use a basic calculated field for a simple profit margin, a quick table calculation for on-the-fly analysis, or a robust LOD expression for complex comparisons, understanding how to build ratios transforms your dashboards from static reports into dynamic decision-making tools.

Learning Tableau syntax like FIXED and COUNTD() is powerful, but sometimes you just need a fast answer without building formulas. Here is where we designed Graphed to simplify the process. Instead of manually creating calculated fields, you can connect your data sources (like Google Analytics or Shopify) and just ask in plain language, "what's my conversion rate per campaign?" or "create a dashboard showing my profit ratio by product." We build the visualizations for you, letting you get straight to the insights.