Count Distinct Totals and Percentages

Data Pipeline
  1. Start with the initial layer by dropping User Id into the Measures field and Created Date into Dimensions. Change the time bucket of Dimensions to Month. We also filtered for the data from the last six months in the Filters field.
    Create chart
  2. Clone the first layer you just created and delete the column in the Dimensions field.

    Clone Dataset 1 and delete the contents of the Dimensions field

  3. In the Pipeline, add an Add Column step to the Dataset 1 Clone layer by clicking the grey plus sign underneath the blue Dataset 1 Clone. Note: Make sure you clicked Run Query before trying to add a column in the Pipeline. Name the new column “Total Distinct” and choose Custom formula with the formula ‘Total Distinct.’ Click Apply and Close.

    Add Column step to Dataset 1 Clone

  4. Add another Pipeline step to Dataset 1 Clone and choose Reorder Columns to make Total Distinct the first column. Make sure to run the query before trying to reorder or your new Total Distinct column will not come up as an option.

    Add Reorder Columns step to Dataset 1 Clone

  5. Add another column to Dataset 1 Clone by clicking the plus sign under the other two steps in Pipeline and choosing Add Column. It doesn’t matter too much what you choose to name this column since eventually we will be hiding it. Again, choose Custom formula; the formula is “Count of distinct Users User Id.” It should pop up as you start typing.

    Add another Column step to Dataset 1 Clone

  6. Click the box that says Merge Datasets in the pipeline and select the Merge Type as Union.

    Merge datasets with Union

  7. Below the merge, add a column by clicking the plus button and choosing Add Column. Name this one “% of Total” and select the Formula Type as Aggregation. The Aggregate Function is SUM using the column Total 2. Hit Apply and Close.

    Add Column step under the Merge

  8. Hide the column Total 2 now by clicking the plus button under the column added in step 7 and clicking Hide Columns and choosing Total 2.

    Hide Columns step under Merge

  9. Click the same plus button and then choose Edit Column. Choose the % of Total column and type in the following custom formula: 1.0 * “Count of distinct Users User Id.” It should pop up as you type.

    Edit Column step under Merge

  10. Click the blue Run Query button and then scroll up to click the Settings button above your chart. Under the Columns tab, click the blue +Add New Formatting Rule button. Change Format Column to % of Total and change the Data Format to Percentage.

    Edit Chart Settings when finished


Related Data Pipeline Help Articles

See more