% formula where numerator adjusts with filtering but the denominator does not

Question & Answer
,
1

I’m new to QuickSight and the community. I’m trying to produce a KPI (in business terms) that is:

distinct_count({case_id} (filtered) / distinct_count({case_id} (unfiltered)

The numerator will change as filters apply, but the denominator will not. However the denominator will increase as new cases are created.

2

Hi @simplyconvert

You could use the PRE_FILTER calculation level. Here’s an example

Numerator
distinct_count(city)

Denominator
distinctCountOver(city, , PRE_FILTER)

Visual:

image
image578×241 13.2 KB

More details available in the documentation

1 Like
3

Hi @simplyconvert -

You will want to use a LAC-W function with the PRE_FILTER argument. This is similar to LODs if you have Tableau experience. Check out:

and this diagram for the order of evaluation in QuickSight is useful when you are first starting with LAC calcs.

Hope this helps,
Rob

4

Giridhar,

I still ran into mismatched aggregation - but it lead to the correct calculation. I appreciate it

1 Like