quicksight average count, receiving 'unavailable' on all rows

user_1089494 0
Viewed 699

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
amazon-quicksight amazon-web-services
3 Answers

Calculated fields will show as unavailable in the dataset as they usually rely on the visualisations using the data.

By putting this in a table visualisation in an analysis you can get what you are expecting to see

user_152441510

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

user_152441510

Use

countOver({deal_id}, [{schema name}], PRE_FILTER) / avgOver(distinctCountOver({deal_id}, [{schema name}], PRE_FILTER), [{schema name}], PRE_FILTER)

as a calculated field and Average the value (as shown in the screenshots), this will get the answer you are looking for. I have never heard of distinctCountOver before and there seems to be no documentation on it? But this definitely gets the values you need in (I think) the way you want to calculate them

3

user_152441510
Related