Hi Quicksight Community,
I am struggling to find an equivalent of this formula that works in Tableau but wont work on quicksight -
Formula -
If {metric_type} = ‘Interger’ then sum({metric_num})
else sum({metric_num})/sum({metric_den})
end
I would truly appreciate any help here!
Thank you!
Hi @Monica,
Can you try this?
ifelse(
{metric_type} = ‘Interger’,
sum({metric_num}),
sum({metric_num})/sum({metric_den})
)
If it doesn’t work, let me know what error you get.
Hi @Monica - Welcome to AWS QuickSight community and thanks for posting the question. Can you please share some sample input and expected output so that we can guide you the right solution. Also please try to implement @David_Wong suggestion as well.
Regards - Sanjeeb
Hi @Sanjeeb2022 ,
Thank you for your response.
Here’s an example of what I’m hoping to derive in my calculated field -
Let’s say I have 3 columns with values -
day site metric_name metric_type metric_num metric_den
2023-08-01 A cost INTEGER 3 1
2023-08-01 A cost_per_hour PERCENTAGE 6 3
2023-08-01 B cost_per_hour PERCENTAGE 4 2
Output I am hoping to get -
when Integer - sum (metric_num) only = 6
when anything other than Integer, sum(metric_num) / sum(metric_den) = (6+4)/(3+2)
Hi @David_Wong ,
Thank you for your response! I tried your solution and unfortunately it doesnt seem to work 
Here is the error I’m getting -
Sorry, I was just converting the syntax for you from Tableau to QuickSight. I realize now that it won’t work because your condition is based on a single value in each row of the dataset, and you’re trying to apply that condition to aggregate multiple rows.
Even in Tableau this would give you an error:
Can you try this instead?
ifelse(
{metric_type} = ‘Integer’,
sumOver({metric_num}, [{metric_name}], PRE_AGG),
sumOver({metric_num}, [{metric_name}], PRE_AGG) / sumOver({metric_den}, [{metric_name}], PRE_AGG)
)
This is assuming you want to show your result in a table visual with 2 columns like this:
Depending on the dimensions in your visual, you may have to adjust the partition used in the sumOver.
@David_Wong -I do have a follow up question that I am hoping you can help me with fingers crossed
Your solution works great for my current use case and I built a visual like -
metric_name 8/1/23 8/2/23
cost 30 40
cpph 3.4 4.5
But if i want to add additonal columns, I understand I can add them to sumOver, which i tried and it works well.
However, if I want to expand and collapse data in the visual, the calculation does not seem to change per my selection.
I am hoping for data to change to table below when I expand to site level view
metric_name site 8/1/23 8/2/23
cost A 30 40
cpph A 3.4 4.5
cpph B 2.1 4.6
and revert to original when I collapse back to just metric level view. Is there any solution for this ?
Do you mean that you have your dates as columns?
When you collapse the rows, you want your partition to be [{metric_name}, {date}]. When you expand the rows, you want it to be [{metric_name}, {date}, site] but you can’t have the partition dynamic like that in QuickSight. You’ll have to create 2 different calculated fields and use them in 2 separate visuals.