KNOWLEDGE BASE

Calculating a Weighted Count Distinct


Published: 06 Jun 2019
Last Modified Date: 23 Sep 2019

Question

How to calculate a weighted count distinct.

Environment

  • Tableau Desktop 2019.2.1
  • Windows 10
  • Tableau Data Engine

Answer

Create fraction calculation using Level of Detail Expressions at two different granularities. The numerator should include the dimensions in the view as well as the ID field.

The following calculation is used in the attached sample workbook, to find the Weighted Count Distinct of [Order ID] along [Category]:
{FIXED [Category], [Order ID]: SUM([Number of Records])}
/
{FIXED [Order ID]: SUM([Number of Records])}

 

Additional Information

In the context of this article, Weighted Count Distinct means counting the number of occurrences of an ID along a specific value of a dimension, and dividing that count by the total number of occurrences across all values of the dimension.

IDs that appear along multiple values of a dimension are counted fractionally in each.

  • For example, if a specific [Order ID] in the attached sample workbook has two records where the [Category] is "Furniture", but a total of 5 records across all [Category] values, its value under furniture will be 2/5, or 0.4.
  • Unlike a regular Count Distinct calculation, if these weighted Counts Distinct are all added together, the result is the same as the Count Distinct across the entire data set.

Note: the SUM aggregation should be applied to the entire expression, and not to the numerator and denominator separately.



Discuss this article... Feedback Forum
Did this article resolve the issue?