How do you run Distinct Sum or Total using COUNTD function in Tableau?

Running a Distinct Sum can come in handy when calculating a rolling sum based on the number of unique customers, products, and so on. It is a powerful expression that is not used very often but useful enough for some cases. Running a Distinct Sum is a bit tricky to implement with functions, so we’ll go through the steps to make it more transparent. The following will be an example of using this expression:

RUNNING_SUM(

    COUNTD(

    IF MONTH([DueDate]) = 

    {INCLUDE [ProductID]: 

    MIN(

        {EXCLUDE MONTH([DueDate]): 

    MIN(

        MONTH([DueDate]))})} THEN [ProductID] END))

What’s going on here?

RUNNING_SUM( – here we need to indicate what we will do with the result of the calculations that will occur in the next steps

COUNTD( – this is an aggregating function that will count the number of unique ProductIDs sold in a period, then we will use conditions to determine unique productIDs

IF MONTH([DueDate]) =  – since we chose the detail by month, we need to calculate the Month from the DueDate; for this, we use the MONTH function. This is the beginning of a logical expression.

{INCLUDE [ProductID]: MIN( – in this boolean expression, each month should be compared to the minimum month with detailed level INCLUDE by ProductID

{EXCLUDE MONTH([DueDate]): MIN(MONTH([DueDate]))})} – here we exclude those months for which the ProductID has already been calculated

THEN [ProductID] END)) – then we display the result of the logical expression, which is already enclosed in the CUNTD and RUNNIND_SUM function

Distinct Sum is a powerful expression in Tableau for calculating a rolling sum based on the number of unique customers, products, and so on.

Up Next:

Read How do I make a Calculated Field with a ‘Between’ formula in Tableau?