4.4 Aggregation Rules, Granularity & Dealing with the Asterisk (ATTR)

4.4 Aggregation Rules, Granularity & Dealing with the Asterisk (ATTR)

Tableau's calculation engine operates on a strict hierarchy of data granularity. Every calculation in Tableau is compiled and executed at one of two fundamental computation levels: Row-Level (Unaggregated) or Aggregate. Failing to understand the boundary between these two computation tiers is the leading cause of broken formulas, calculation compiler errors, and mathematically flawed dashboard metrics.

Furthermore, when Tableau aggregates dimensional attributes or evaluates secondary blended data sources, it invokes the Attribute (ATTR) function. Understanding how ATTR() operates, why it generates the infamous asterisk (*), and how to resolve it is a cornerstone topic on the Salesforce Certified Tableau Data Analyst exam.

---

Row-Level Calculations vs. Aggregate Calculations

The fundamental distinction between row-level and aggregate calculations lies in when and where the computation occurs relative to the visualization's Level of Detail (LOD):

ROW-LEVEL CALCULATION:     [Row 1: Sales - Cost] ---> [Row 2: Sales - Cost] ---> Then Aggregated in Viz
AGGREGATE CALCULATION:     SUM([Sales]) across group  /  SUM([Cost]) across group  ---> Computed at Viz Grain

1. Row-Level (Unaggregated) Calculations

• Execution: Evaluated on every single transactional row in the underlying database before any summary aggregation takes place.
• Syntax Example: [Sales] - [Cost] or [First Name] + ' ' + [Last Name] or [Order Date] + 7.
• Data Pane Representation: Appears in the Data pane with an unaggregated symbol (# or Abc). When dragged onto a shelf, Tableau wraps it in a default aggregation (e.g., SUM([Profit_Margin_Row])).

2. Aggregate Calculations

• Execution: Evaluated across the aggregated sums, averages, or counts of records belonging to each mark in the visualization. The grouping is determined dynamically by the dimensions present on Rows, Columns, and Detail shelves.
• Syntax Example: SUM([Profit]) / SUM([Sales]) or AVG([Discount]) * 100.
• Data Pane Representation: Appears in the Data pane with an AGG indicator preceding the field name. When dragged onto a shelf, it displays as AGG([Profit Ratio]) and cannot be re-aggregated with SUM or AVG.

---

Choosing the Correct Ratio Grain

One of the most dangerous and common errors in analytics is calculating a ratio at the row level and then averaging it on the canvas. Consider a simple dataset with two transactions in a region:

The Incorrect Approach (Averaging Row-Level Ratios):

If the analyst writes the row-level calculation [Profit] / [Sales] and drags it onto the view as AVG([Profit Ratio]), Tableau calculates the arithmetic mean of the two percentages: $\text{Average} = \frac{50\% + 10\%}{2} = 30.0\%$ The two results answer different questions. The 30.0% value is the unweighted average of transaction margins. The 10.4% value is total profit divided by total sales, which is the overall profit margin for this example. Choose the formula from the metric definition; do not label an average of row ratios as an overall margin.

The Correct Approach (Aggregate Calculation):

// Overall profit divided by overall sales:
SUM([Profit]) / SUM([Sales])

Tableau first sums profit and sales at the view partition, then divides the aggregates ($1,050 / $10,100 = 10.4%). That is the appropriate calculation when the business definition is overall profit divided by overall sales; a metric defined as the mean transaction margin would intentionally use the row-level ratios instead.

---

Deconstructing the Error: "Cannot Mix Aggregate and Non-Aggregate Arguments"

Every Tableau developer encounters this infamous error message:

"Cannot mix aggregate and non-aggregate arguments with the function '...'."

Why the Error Occurs

This error arises when an expression attempts to evaluate an unaggregated, row-level field alongside an aggregate calculation within the same scalar operation. For example:

// INVALID: Mixing row-level [Region] with aggregate SUM([Sales])
IF [Region] = 'West' THEN SUM([Sales]) END

// INVALID: Mixing row-level [Sales] with aggregate AVG([Sales])
[Sales] > AVG([Sales])

In the first formula, [Region] is evaluated for each individual row, while SUM([Sales]) is evaluated across all rows in the partition. Tableau's compiler cannot decide whether the resulting output belongs on a single row or as a summary mark for the partition.

How to Resolve the Error

Depending on business intent, analysts resolve this error using one of three proven strategies:

1. Strategy 1: Move the conditional logic inside the aggregation (Row-level test):

   // Valid: Evaluates IF row-by-row, then sums the resulting rows
   SUM(IF [Region] = 'West' THEN [Sales] END)
   

2. Strategy 2: Aggregate the non-aggregate field using ATTR():

   // Valid: Both terms are aggregated
   IF ATTR([Region]) = 'West' THEN SUM([Sales]) END
   

3. Strategy 3: Use a Level of Detail (LOD) Expression to compute the aggregate as a row-level value:

   // Valid: FIXED LOD expression returns a row-level scalar across all records
   [Sales] > { FIXED : AVG([Sales]) }
   

---

Counting Aggregations: COUNT() vs. COUNTD()

Tableau provides two primary counting aggregations that differ sharply in mathematical behavior and computational overhead:

[!TIP] Performance Best Practice: Use COUNTD() when the question requires distinct entities, but test it on the target connection and view grain. A high-cardinality distinct count can cost more than COUNT(), and the source engine, filters, indexes, extract design, and grouping dimensions all affect the result.

---

The Attribute Function (ATTR) & Resolving the Asterisk (*)

The Attribute (ATTR) function is Tableau's built-in mechanism for testing whether all rows within an aggregated group share a single, identical value.

The Mathematical Formula of ATTR()

Under the hood, Tableau evaluates ATTR([Field]) using the following exact logical formula:

IF MIN([Field]) = MAX([Field]) THEN
    MIN([Field])
ELSE
    '*'
END

How ATTR() Evaluates:

1. Single Unique Value: If every record in the partition has the same value (e.g., all rows have State = 'Texas'), then MIN('Texas') equals MAX('Texas'). Tableau successfully displays 'Texas'.
2. Multiple Distinct Values: If the partition contains records with different values (e.g., some rows have 'Texas' and others have 'California'), then MIN('California') does not equal MAX('Texas'). Because Tableau cannot display multiple distinct values in a single cell or mark, it renders an asterisk (*).
3. All Nulls: If every record in the partition is NULL, MIN() and MAX() evaluate to NULL, and Tableau displays a blank or Null.

+-----------------------------------------------------------------------------------+
|                         HOW ATTR() EVALUATES A PARTITION                          |
+------------------------------------+--------------------------+-------------------+
| Partition Contents                 | MIN() vs MAX()           | Visual Output     |
+------------------------------------+--------------------------+-------------------+
| ['Texas', 'Texas', 'Texas']        | MIN = MAX ('Texas')      | 'Texas'           |
| ['California', 'Texas', 'Oregon']  | MIN != MAX               | '*' (Asterisk)    |
| [NULL, NULL, NULL]                 | Both NULL                | NULL (Blank)      |
+------------------------------------+--------------------------+-------------------+

Common Scenarios Where the Asterisk Appears

• Data Blending: When an analyst drags a secondary dimension into a worksheet, Tableau automatically aggregates it as ATTR(). If multiple secondary records link to a single primary mark, an asterisk appears.
• Tooltips: If a dimension is not present on Rows, Columns, or Detail, adding it to the Tooltip shelf defaults to ATTR([Field]). If the mark represents multiple categories, hovering over the mark displays an asterisk in the tooltip.
• Aggregate Calculated Fields: When authoring formulas that compare dimensions alongside aggregated measures.

Concrete Solutions to Resolve the Asterisk (*)

1. Add the Dimension to Detail: Drag the field from the secondary source or Data pane onto the Detail shelf on the Marks card. This increases the view's Level of Detail so that each mark represents a single unique value, allowing ATTR() to return the actual string instead of *.
2. Change the Aggregation to MIN() or MAX(): If displaying any single representative value is acceptable to business stakeholders, right-click the pill and change its aggregation from Attribute to Minimum or Maximum.
3. Migrate to the Modern Data Model (Relationships): Replace legacy Data Blending with Relationships in the Logical Layer. Relationships preserve each table's native level of detail and eliminate the forced ATTR() aggregation imposed by blending.