Counting Dimension Members that Meet a Condition

Published: 05 Sep 2017
Last Modified Date: 20 Jul 2023

Question

How to calculate the count of dimension members that meet a condition.

Environment

Tableau Desktop

Answer

In general the formula to count dimension members that meet a condition is:

{ FIXED [Dimension] : SUM(
IF <Condition>
THEN 1
ELSE 0
END
) }

There are several use cases that may use this general formula. Below are some examples using the sample data set Superstore.

CLICK TO EXPAND SOLUTION

Example 1: Using a Range of Values with Parameters

In this example the view contains [Category] and [Segment] on Rows, and the goal is to count the number of customers with sales between two parameter values for every combination of category and segment.

Select Analysis > Create Calculated Field
In the Calculated Field dialog box that opens, do the following, and then click OK:
1. Name the calculated field. In this example, the calculated field is named "# of Customers with Select Sales"
2. In the formula field, create a calculation similar to the following:
  IF { INCLUDE [Customer Name] : SUM([Sales])} >= [Sales Lower Limit]
  AND { INCLUDE [Customer Name] : SUM([Sales])} <= [Sales Upper Limit]
  THEN 1
  ELSE 0
  END
Drag [# of Customers with Select Sales] to Text on the Marks card

CLICK TO EXPAND SOLUTION

Example 2: Using a Text Value

In this example the view contains [Order ID] and [Product Name] on rows, and the goal is to count orders that contain the product "Staple holder."

Create a calculated field with a name like "# of Orders with a Staple holder" with a calculation similar to the following:
{ FIXED [Order ID] : MAX(
IF [Product Name] = "Staple holder"
THEN 1
ELSE 0
END)}
Drag [# of Orders with a Staple holder] to Text on the Marks card

CLICK TO EXPAND SOLUTION

Example 3: Creating Categories

In this example [State] has been added to Text, and the goal is to categorize states by the number of cities with a negative profit.

Create a calculated field with a name like "# of Unprofitable Cities" with a calculation similar to the following:
{ FIXED [State] : SUM(
IF { INCLUDE [City] : SUM([Profit]) } < 0
THEN 1
ELSE 0
END
) }
Create a calculated field with a name like "Categories based on city profit" with a calculation similar to the following:
IF [# of Unprofitable Cities] = 0
THEN "All cities have positive profit"
ELSEIF [# of Unprofitable Cities] = 1
THEN "One city has negative profit"
ELSE "Multiple cities have negative profit"
END
Drag [Categories based on city profit] to the Columns shelf

CLICK TO EXPAND SOLUTION

Example 4: Using Table Functions

In this example, the view is already filtered to show the top 10 customers by sales per category per segment. The goal is to highlight any category/segements that have more than two customers with negative profit.

Create a calculated field with a name like "3+ Top 10 Customers w/ Neg Profit" with a calculation similar to the following:
IF
WINDOW_SUM(
IF SUM([Profit]) < 0
AND [Sales Rank] <= 10
THEN 1
ELSE 0
END ) > 2
THEN "Three or more customers with negative profit"
ELSE "Good standing"
END
Drag [3+ Top 10 Customers w/ Neg Profit] to Color on the Marks card
Right-click [3+ Top 10 Customers w/ Neg Profit] on Color and select Compute Using > Customer Name

Additional Information

Notes on Example 1:

The INCLUDE expression is necessary because [Customer Name] is not in the view
SUM([Sales]) is aggregated up to the level of detail in the view, which is [Category] and [Segment] in this example, plus [Customer Name] because the INCLUDE expression lists [Customer Name]
The ["# of Customers with Select Sales] calculation will evaluate if the sales per customer is below [Sales Upper Limit] and above [Sales Upper Limit]. If the sales per customer is in the specified range, then this calculation will return 1, which when added up acts as a count of customers
This calculation can also be written with a COUNTD()

Notes on Example 2:

The IF statement reads over every record in the underlying data set to check if [Product Name] is "Staple holder". If so, the calculation returns 1 for that record. Then the FIXED expression will return the maximum of the 1s and 0s for every record with the same value of [Order ID].
In Superstore, there is only 1 record for every unique combination of [Product Name] and [Order ID], which means the MAX() could be replaced with SUM() and return the same result because summing up 1 value is the same as taking the maximum of 1 value.

Notes on Example 3:

The INCLUDE expression will return the sum of profit per city per state because [State] is in the FIXED expression. The calculation will then return a 1 for every unprofitable city, which is a way to count the cities.

Notes on Example 4:

This example must use table calculations rather than level of detail (LOD) expressions because table calculation filters do not filter the underlying data, therefore we must add a condition inside the calculation for rank. However RANK() is a table function and cannot be used inside of a LOD calculation.
Table Calculations are affected by every non-aggregated dimension in the view. If the table calculation is not returning the correct results this may be due to how the table calculation is computed by the dimensions in the view. To resolve this issue, change the compute using option. For more information, please see Transform Values with Table Calculations