AWS Quicksight - How do I group by sum and then average?

user_8298828 0
Viewed 4382

I've just started using Quicksight and I can't for the life of me figure this out.

Let's say I have a table that goes like this :

date country color nb_sales
10-11 USA Black 10
10-11 USA Blue 5
10-12 USA Blue 20
10-11 UK Black 10
10-12 UK Black 15
10-11 UK Blue 15

What I want is the average daily number of sales by country, preferably in a pieplot :

country avg_nb_sales
USA 17.5
UK 20

So I need first to group by date, country and sum the ratings and then once this aggregation is done I need the average by country. I thought I should be using avgOver(sum(ratings), country) but I can't get it right.

So how do I achieve that ?

I thank you for your time.

amazon-quicksight
1 Answers

I have managed to get the result expected though not sure that it is the best way to do it (especially if you have some dates when you get sales only for one country):

avgOver(sum(sales), [country])/distinct_count(date)

enter image description here

user_33719450
Related