Question

Unique Counting w/ Filters

  • 4 May 2023
  • 1 reply
  • 150 views
MKohli
Rank
Userlevel 4
Badge +8

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

Switch(
IF('Ending ARR' <> 0 and ISDEFINED('Ending ARR'), 1 , blank)[remove sum: 'Product Type'],
  1,1,
  2,0.5,
  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

Fabien
Rank
Userlevel 3
Badge

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 ?

Reply


Ask your question here!

Terms & ConditionsCookie settings