Pivot Table Calculation Item

ypurcaro

New Member
Joined
Mar 7, 2013
Messages
20
Hello,
I am trying to build a dashboard on the time keeping. So, the raw data has header such as Date, Categories, Hours and Sites. Under the Categories, we have "OT, "NT" and "Abs" and under Sites, we have MW, NC, and WE.
So, I have to find out the total of Over time, Normal Time and Absent time in percentage. To calculate the % of OT, here is the calculation OT/(OT+NT). Now, my problem is, on the pivot table, the OT, NT, ABS are items but not field. So, I could still calculate the items but then after I got the calculation, I won't be able to group the date month, quarter and year. Anybody has any idea what should I do, I'm so frustrated.

Thank you.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
In pivot table put date on rows, categories on columns, hours in values.
after that, in date column by the help of filter you can find date as monthly yearly
 

ypurcaro

New Member
Joined
Mar 7, 2013
Messages
20
In pivot table put date on rows, categories on columns, hours in values.
after that, in date column by the help of filter you can find date as monthly yearly
Hi Rajesh,

Yes, I tried that as well, but it won't let me group as QUARTER 1, QURATER 2...it gives me the whole 3 months in daily.

Thanks again for replying.
 

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
mr excel.xlsx
ABC
2DateQQ
314/12/201944
423/11/201944
529/02/202011
612/10/201944
728/01/202011
827/12/201944
906/05/201922
Sheet4
Cell Formulas
RangeFormula
B3:B9B3=CHOOSE(MONTH(A3),1,1,1,2,2,2,3,3,3,4,4,4)
C3:C9C3=MONTH(MONTH(A3)&0)

You just need to insert one more column to convert date into quarter
 

ypurcaro

New Member
Joined
Mar 7, 2013
Messages
20

ADVERTISEMENT

Mr. Excel,

So, do I enter this formula to the raw data before I do anything?

Thanks so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,048
Members
412,565
Latest member
roberttaekim
Top