quicksight average count, receiving 'unavailable' on all rows

Question

I have a table like this on my spice:

schema name  deal_id
a            1
a            1
a            2
b            1
b            2
b            3

I need to generate a diagram showing the average number of deals per each schema,

this is what I think should make it work:

avgOver(count({deal_id}), [{schema_name}])

but it doesn't in the dataset I see the column with all 'unavailable' values.

I get the same result with this:

avgOver(countOver({deal_id}, [{schema_name}]), [{schema_name}])

I'm not sure what's the problem,

the calculated result I need for my chart would be like (with the data above):

schema name         average number of deals
a                   1,5
b                   1.0

Answer 1

Choose a visualisation that has a group by field well and put the schema name here, put the deal_id in the value field well and choose the aggregation type of average to achieve what you want