How to write Percent of Total calculation , excluding dashboard filters in denominator?

Question & Answer
, , ,
1

Hello!

I am trying to get a user_id % of total calculation. Specifically, I am trying to get the user_id count of users I left joined over the primary datasource. The calculation I have works as expected, but goes to 100% when I try to filter by the user age. Please see the following current calculation:

distinct_count({user_id[joined_dataset]}) / distinct_count({user_id})

After doing some research on level-aware calculations, I’ve also tried the following:

distinctCountOver({user_id[joined_dataset]}, [Age], PRE_AGG)
/
distinctCountOver({user_id}, [Age], PRE_FILTER)

While the above calc reduces the % of total, it still is giving me incorrect percentages. I also get an error when I try to do a distinctCountOver in the denominator while leaving a distinct_count in the numerator (mismatched aggregation).

Any help here would be greatly appreciated!

2

Hi @bmac412

Three questions to help me understand the use case better.

1- when filtering by age, is your objective to filter both numerator AND denominator by age?
2- Is the age in the main table or the left joined table?
3- What type of visual are you looking to represent this calculation in?

Thanks!

3

Ultimately assuming you dont want to filter denominator you could do this.

1- formula for denominator:
formula name: prefilter
image

2-formula for %
image

Please let me know if this achieves your goal.

thanks!

1 Like