# Create Avg & Sum in Povit Table for ParticularValue

**URL:** <https://community.amazonquick.com/t/create-avg-sum-in-povit-table-for-particularvalue/10192>\
**Category:** Q&A\
**Tags:** analysis, calculations, quick-sight\
**Created:** [February 20, 2023, 6:37am UTC](https://community.amazonquick.com/t/create-avg-sum-in-povit-table-for-particularvalue/10192 "2023-02-20T06:37:53Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![bpradeepkumar](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bpradeepkumar](https://community.amazonquick.com/u/bpradeepkumar)\
**Post date:** [February 20, 2023, 6:37am UTC](https://community.amazonquick.com/t/create-avg-sum-in-povit-table-for-particularvalue/10192/1 "2023-02-20T06:37:53Z")

</div>

i have taken Pivot table as mentioned below  
 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/3/33f3a7a302124bf19840b012389357a8fb6628fb.png)  
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  
 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/6/6d65a6d93409a5e1494aecf0547f02265da77447.png)  
when collapse building then count should show 110 rather than average. as mentioned below screenshot.  
 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/c/cbaf28c336fc9a23e2eb6f53e98eace6609f389e.png)  
but i am getting the average count like 55. Please suggest me on this.

---

<div class="post-metadata">

**Author:** ![Koushik\_Muthanna](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/koushik_muthanna/32/3290_2.png) [@Koushik\_Muthanna](https://community.amazonquick.com/u/Koushik_Muthanna)\
**Post date:** [February 20, 2023, 8:30am UTC](https://community.amazonquick.com/t/create-avg-sum-in-povit-table-for-particularvalue/10192/2 "2023-02-20T08:30:16Z")

</div>

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.

 ![Screenshot 2023-02-20 at 09.29.56](https://d1wl4jav5dp96l.cloudfront.net/original/2X/1/1222a2f08024e2907ad25a012e56fb8efd961a14.png)

Regards,  
Koushik

---

<div class="post-metadata">

**Author:** ![raju123](https://avatars.discourse-cdn.com/v4/letter/r/9f8e36/32.png) [@raju123](https://community.amazonquick.com/u/raju123)\
**Post date:** [February 20, 2023, 11:20am UTC](https://community.amazonquick.com/t/create-avg-sum-in-povit-table-for-particularvalue/10192/3 "2023-02-20T11:20:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![qinya](https://avatars.discourse-cdn.com/v4/letter/q/b4bc9f/32.png) [@qinya](https://community.amazonquick.com/u/qinya)\
**Post date:** [March 10, 2023, 10:55pm UTC](https://community.amazonquick.com/t/create-avg-sum-in-povit-table-for-particularvalue/10192/4 "2023-03-10T22:55:31Z")

</div>

Hi @bpradeepkumar @raju123 ,

[level-aware calculations](https://docs.aws.amazon.com/quicksight/latest/user/level-aware-calculations.html) 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.40.41 PM](https://d1wl4jav5dp96l.cloudfront.net/original/2X/2/28ec7aeefc548f29b0489e2497c8c43cbe915b20.png)  
 ![Screen Shot 2023-03-10 at 5.43.39 PM](https://d1wl4jav5dp96l.cloudfront.net/original/2X/1/1e848ec453957cf6a262cd694d3ab2984b85fe24.png)  
 ![Screen Shot 2023-03-10 at 5.44.01 PM](https://d1wl4jav5dp96l.cloudfront.net/original/2X/d/da6c05096e5bb60260f4843f049ee0ccfc312e84.png)

Regards,  
Qinya

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

---

<div class="post-metadata">

**Author:** ![abacon](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/abacon/32/2291_2.png) [@abacon](https://community.amazonquick.com/u/abacon)\
**Post date:** [March 16, 2023, 4:25pm UTC](https://community.amazonquick.com/t/create-avg-sum-in-povit-table-for-particularvalue/10192/5 "2023-03-16T16:25:04Z")

</div>

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 Quick Sight Community Q&A Forum.  
Andrew