Create Avg & Sum in Povit Table for ParticularValue

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.

1 Like

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.


1 Like

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.

Screen Shot 2023-03-10 at 5.44.01 PM


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.