Unique Counting w/ Filters

  • 4 May 2023
  • 1 reply

Userlevel 3
Badge +2
  • Enthusiastic Pigmenteer
  • 14 replies

Hi Community,


Wondering if someone has been able to manage counting unique customers for a metric dimensioned by customer & product.

We have an ARR Metric defined by Customer ID, Product and Month. I would like to have a count by month of the unique customers which have an ARR value. The standard approach using the formula below is effective in having total customer accounts but I am then unable to filter on the products available.

IF('Ending ARR'[remove sum: 'Product Type'] <> 0 and ISDEFINED('Ending ARR'[remove sum: 'Product Type']), 1 , blank)

I worked to try and resolve this issue by leveraging the below formula which does allow me to filter on products and not double count total logos but when filtering on a single product the number becomes understated. Exec filtering on a product that is part of a set>1 would yield 0.5 or 0.33 vs 1

IF('Ending ARR' <> 0 and ISDEFINED('Ending ARR'), 1 , blank)[remove sum: 'Product Type'],
  3, 1/3)*IF('Ending ARR' <> 0 and ISDEFINED('Ending ARR'), 1 , blank)

Overall the goal is to have my data operate like formula #1 but still maintain the ability to filter(page select) on product type and not double count the number of logos.

1 reply

Good afternoon!

Thanks for your question.
From what I understand of your expectation, you could use the REMOVE COUNTUNIQUE modifier on your Customer dimension.
Here is what I have set up as a test:
A table ARR dimensioned by Month, Customer and Product:

And the table dimensioned by Month and Product, counting the unique Customer per Product (in which you can filter by product):

Does it answer your need ?