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.
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 ?