Direct query resulting in infinity calculation

[jbonjura]

I'm using a direct query of a redshift data source and I'm having two issues.

1. A calculation is sometimes but not always calculating incorrectly and showing an output of infinity, you can see on the table below, particularly on the Paid search CTR that the rows around the highlighted one are calculating correctly but for that row (and a number of others not shown it is resulting in an infinity result).  The calculation for ROAS is sum(sum_ga_transaction_revenue)/sum(sum_cost) and the calculation for CTR is sum(sum_clicks)/sum(sum_impressions).

2. The underlying data has values with decimal points but when it is summed as in the table below it is rounding off the values.

Any help would be appreciated!

[v-yuta-msft]

@jbonjura,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@jbonjura,

Change the type of value in columns sum_ga_transactionrevenue and sum_cost from currency to number type. In addtion, check if the sum_clicks and sum_impressions are both number type.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.