I have two tables that are grouped by Category and Category 2 with a distinct count of IDs and a % of of the count. I am trying to calculate an estimate population if you were to scale the raw count of IDs to the total population of IDs in the dataset.
Currently, I have calculated the estimate population in the table using the following calculated field:
which is the percentage in the table multiplied by the total population before any filters. The calculated field works without filters.
I am having an issue when I start applying filters. In the example below, I want to filter the first table by F in Category 2.
When I filter by F, I want to calculate an intermediate estimate population which will be the actual % proportion F multiplied by the total population (with no filters), and then with the scaled population, perform the same calculation as above in the calculated field to get the filtered estimate population.
Estimate scaled population of F = % proportion of F * total population (no filters)
Estimate Population (Category 1) = % category 1 * estimate scaled population of F
I’m not sure I understand what the problem you are running into is? When you add the filters for F in Category 2, is the problem that it is affecting the values in the first Category or that you the % proportion of F is giving an incorrect value when you apply the filter?
When I filter table 1 by F, I get the wrong value in calculated for the estimate. Essentially, I don’t get the correct percentage. The data is from a survey so a person could answer the question for category 1 but not 2, and vice versa as well as answering both.
Essentially I want to perform the following calculation but not sure how to exclude a category in the group-by level. I have excluded a category in both tables for ‘No Answer’ which is why I think the calculation is giving the wrong value for the estimate when filtering as the group-by level will still include that category.
I think this is essentially the calculation I want to make, where category_2 will change when I filter table 1.
// % proportion of category_1 in table 1
(avg(distinct_count({id}, [category_1}])) / avg(dstinctCountOver({id}, [], PRE_AGG)))
*
// estimate population F
(
// total population
avg(distinctCountOver({id}, [], PRE_FILTER))
*
// % proportion of F
avg(distinct_count({id}, [category_2 = F}])) / avg(dstinctCountOver({id}, [category_2 (excluding 'No Answer' )], PRE_FILTER))
)
Those calculations look like they should work but I haven’t tested something similar on my side. When you tried these did they return any kind of error?
You could also try applying a 1 or 0 value in an ifelsestate for if category 2 = f or is blank:
ifelse(
category_2 = 'F', 1,
category_2= ' ',
0)
This is basically saying that if category_2 field has a blank string in the row that it has a value of 0 but you could use any integer or even do True False.
