Difference from total

Q&A
, , ,
1

Hi Team,

I have a dataset that has category, days and month like this.

image

In a pivot table, I’m calculating the average number of days by category. I want to create another calculated field that measures the difference between the average of days of each category from the total average of days for that month. For category A in January, the calculated field should be 3 (10 - 7) and for category B it should be -3 (4 - 7), like the screenshot below.

image
image760×461 61.9 KB

Any suggestions would be appreciated.

2

Try this please

avg({days},[{Category},{Month}]) - avg({arrival_timestamp_int},[{Month}])
3

Hi Max -

Thanks for the suggestion, I corrected the second avg function (replaced arrival_timestamp_int with days) and it got me slightly closer to what I need. I created individual calculated fields for each portion of the difference and they are displayed just fine in the pivot table.

avg({Days},[{Category},{Month}])

avg({Days},[{Month}])

image
image807×253 11 KB

However, when I calculate the difference between the two (Avg Cat - Avg Month), I get a Level Aware Calculation message in the table.

image
image954×228 10.5 KB

Any other suggestions? Thanks again!

4

Hello @raul_23 !

If you would still like assistance with this, or if you have a new Quick Sight question, feel free to post a new topic in the community so you will be at the top of the priority list for a response from one of our Quick Sight experts.

I am going to archive this topic since it has been open for an extended period of time. If you were able to find the solution please feel free to post it to help the community. Thank you!