KNOWLEDGE BASE

Counting dimension members that meet a condition


Published: 05 Sep 2017
Last Modified Date: 05 Sep 2017

Question

How to calculate the count of dimension members that meet a condition, for example, dimension members that have a measure value greater than zero.

Environment

  • Tableau Desktop

Answer

The attached workbook uses the example dataset Superstore to find the distinct count of customers with a positive profit sum. However, it features a calculation that can be added to any view where you wish to calculate the count of dimension members that meet a certain condition.

  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following:
  3. Name the calculated field. In this example, the calculated field is named “Count of Customers with Positive Profit"
    SUM
    (
    IF { FIXED [Customer Name] : SUM([Profit]) } > 0
    THEN 1
    END
    )
     
  4. Then click OK.
  5. Drag [Count of Customers with Positive Profit] to the Rows shelf.

Additional Information

The FIXED statement always returns the sum of profit for each customer no matter how the view is built. Therefore the calculation evaluates if each customer meets the condition stated.

This calculation can also be written with a COUNTD(); However the COUNTD() function is computationally expensive.
Did this article resolve the issue?