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.
Regards,
Koushik
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.
Regards,
Qinya
Did this answer your question? If so, please help the community out by marking this answer as "Solution!"
3 Likes
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
2 Likes