Calculated Field Problem - Ratio between Filtered and All up metric total

Q&A
, , ,
1

I have a dataset which has multiple dimensions, out of which the important for this problem statement are

reporting_date, metric_name, metric_value, condition

I am trying to build a ratio metric where the formula looks like below
Numerator: sum(metric_value) where metric_name = ‘New Customers’ and condition = ‘FF’ and reporting_date = ‘Period Selected in controls’

Denominator: sum(metric_value) where metric_name = ‘New Customers’ and condition = ‘All up’ and reporting_date = ‘Period Selected in controls’

So, I am trying to find the % of New customers who came through particular condition as a ratio of all up new customers acquired. Condition is a control on the dashboard and to calculate the above metric, it should not impact the denominator. I’ve tried the LCA-A and LCA-W functions without success. Any help is appreciated.

2

Hi @kcakula1

Welcome to the QuickSight community!

To achieve your desired result use the sumIf function, you can create a calculated field for both the numerator and denominator using sumIf to add the conditions.

Example: (Syntax may vary - Adjust the field names from your datasets)

NewCustomers_FF - sumIf(metric_value, metric_name = 'New Customers' AND condition = 'FF' AND reporting_date = {SelectedPeriod})

NewCustomers_AllUp - sumIf(metric_value, metric_name = 'New Customers' AND condition = 'All up' AND reporting_date = {SelectedPeriod})

NewCustomers_Ratio - {NewCustomers_FF} / {NewCustomers_AllUp}

Please refer the below documentation this might be helpful for you.

3

Hi @kcakula1,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial topic?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

4

Hi @kcakula1,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!