Pivot Table Sum issue

[sms2luv]

I have made a pivot table which shows the Csat% and break%.
I have used pivot formula for both.
Like number of csats/total polls for csat% and total minutes of break/total login time.

I have used pivot sum for both.
I am getting data for both properly employee wise.
The grand total of csat% comes as an average of entire employees.
But there is a issue with break%, grand total of break% is not showing as an average, instead it shows the sum of the entire column.

Please guide me, what's wrong here and how can I correct break% grand total.

File is very confidential, so cannot share it.

 

[Chihiro]

Without at least giving us sample file that demonstrate your data set and calculation step. It's very hard to help you.

I'd suggest you upload sample with sanitized data with small sample that still replicates your issue.

 

[sms2luv]

uploading sample file.
please check the sample file pivot table.
csat % shows the average in grand total
however in Break % it shows the total.

 

[Chihiro]

That's because you are using 540 as constant.

Therefore in grand total. Calculated field would be calculated as...
Sum of All Short & Lunch breaks / 540.

To avoid this, add helper column to source table to track 540 (whatever it represents).

 

[sms2luv]

Fantastic bro that worked for me
Thanks a Ton, no words to express your help.

 

[sms2luv]

Sorry to bother you, wanted to ask you one more thing.
If you see the sheet many people are exceeding breaks.
Break% should be less then 12%.

Can I make some formula or VB code so that I can get all names of people exceeding breaks.
For example all people who exceeded break names should come in a cell.

 

[Chihiro]

Just right click anywhere in the Row Label Field.

Filter->Value Filters.

Sum of Break% is greater than 0.12