i have taken Pivot table as mentioned below

When expand all the fields the data shows correctly for Beds each month 60 but when i collapse date each building1 it shows 60 and Building 2 shows 50 as i am taking the average. as mentioned below screenshot

when collapse building then count should show 110 rather than average. as mentioned below screenshot.

but i am getting the average count like 55. Please suggest me on this.

Hi @bpradeepkumar ,

The aggregation has to be set to Sum , you can expand and collapse and the totals should match.

You could drop the same measure again in values and change the aggregation to Average.

Regards,

Koushik

Here i do not want to show sum of all the months which is incorrect, i want to show the average of all the months 60. when i collapse all fields i want to see sum of all building not the average.

Hi @bpradeepkumar @raju123 ,

level-aware calculations may help you with this scenario. Please see below example:

By creating a calculated field - `Num_LAC = avg(Num,[Salesperson])`

, we take average sales number only on the Salesperson level. Then we add it to field wells and select aggregation as Sum.

In your case it would be `avg(Beds, [Building])`

- average beds count only on building level.

Below is the result pivot table. Num is the original column with aggregation set to Average. Num_LAC is the calculated field with level-aware calculation.

Regards,

Qinya

**Did this answer your question? If so, please help the community out by marking this answer as "Solution!**"

Hi @raju123 & @bpradeepkumar,

We hope the reply from @qinya helped you.

I am marking this reply as â€śSolutionâ€ť, but let us know if this is not resolved. Thanks for posting your question on the QuickSight Community Q&A Forum.

Andrew