Sum of Average Order Value

Hi,
I wanna calculate the sum of avo but i can’t perform the calculation with sum(sum…)

Thank you !

Hi @thecuon119 ,

You can’t nest aggregations like that in QuickSight. If you remove the outermost sum, the error should go away but check if it gives you the correct result.

Is sum_app_sales_amount a calculated field or is it raw data from your dataset?

1 Like

yeah i know, sum_app_sales_amount is raw data, it is the revenue

i want to calculate the sum of this one:

sum({sum_app_sales_amount})/distinct_countIf({transaction_order_id}, isNotNull({transaction_order_id} but I really can’t

In what visual type are you trying to show the sum of average order value?

Can you also show a few rows of sample data?

1 Like

Here it is

I’m not sure I understand how the sum of average order value is different from the sum of all order values.

Do you mean you want to add up the AOV for every month?

Do you take a notice of four visuals below ?
This one is just the average value/per order, corresponding to the visual named AVO below while I want to calculate the sum of them in a period i choose based on start date and end date parameters


Therefore, this visual number is incorrect when compared to Total Commission

Can you try this?
sum(avg({sum_app_sales_amount}, [{transaction_order_date}]))

Use the truncDate function on {transaction_order_date} before the above if you want to change the period. For example, if you want monthly average:
sum(avg({sum_app_sales_amount}, [truncDate(“MM”, {transaction_order_date})]))

Not sure why you need to include the condition for isNotNull({transaction_order_id}). Can you have a sales amount if transaction_order_id is null?