Using Distinct Counts

You can derive the distinct count of a category or a date as a new aggregated measure. The aggregated measure does not contain data values in itself, but when it is used in a visualization, it displays the number of distinct values for the data item on which it is based.

For example, you might derive the distinct count of a category that contains city names. If you create a bar chart of the aggregated measure and a category that contains product lines, then the bar chart shows the number of cities where each product line is produced.

To derive the distinct count for a category or date, right-click on a category or date in the Data pane, and then select Derive Distinct Count. The derived measure appears automatically in the Data pane. The name of the aggregated measure is the same as the category or date on which it is based, with the suffix “(Distinct Count).”

Aggregated measures can be used in the following visualization types:

For bar charts, line charts, treemaps, and geomaps, you can right-click on a data point in the visualization, and then select Explore Distinct Values. When you explore the distinct values, a new bar chart is created. The new bar chart displays the values and the frequency of each value.

For example, you might use a derived measure to create a bar chart that displays the number of cities where each product line is produced:

Then, you might want to explore the distinct values for City where Promotional items are produced. By right-clicking on the bar for Promotional, and then selecting Explore Distinct Values, you get a new bar chart that contains the cities where promotional items are produced and the frequency of data for each city.