Pivot table calculate FTE's without summing days in month for every line

[CONORF]

I have a calculated field in the pivot table to take the number of days in the month and multiply by 8. It is working for each individual line but the summary it is adding up the number of days for every line so the formula isn't working. I want the Salary total to be the total of all. Appreciate if anyone has any ideas on how to get it to total?

Calculated Field
Solve Order	Field	Formula
1​	FTE	='Hour''s' /(8*'Payroll Days' )

PIVOT

Sum of FTE			Column Labels
Location	Category	Type	Apr-23​	May-23​
1	Salary	PTO	0.3	0.3
		Regular	5.6	5.6
	Salary Total		2.9	2.9
	Premium	Premium	0.4	0.4
		PRN	0.3	0.3
		Shift Premium	0.0	0.0
	Premium Total		0.2	0.2
	Locum	Locum	0.8	0.8
	Locum Total		0.8	0.8
2	Salary	Holiday	-	-
		PTO	2.5	2.5
		Regular	16.0	16.0
	Salary Total		6.2	6.2
	Premium	ORIENTATION	-	-
		Premium	2.1	2.1
		PRN	1.3	1.3
		Shift Premium	0.8	0.8
	Premium Total		1.1	1.1
	Locum	Locum	6.7	6.7
	Locum Total		6.7	6.7
Grand Total			2.2	2.2

Source Data

Location Number	Type	Category	Month/Year	Hour's	Payroll Days
1	Regular	Salary	Apr-23​	1,116	20
1	PTO	Salary	Apr-23​	35	20
1	Premium	Premium	Apr-23​	97	20
1	PRN	Premium	Apr-23​	102	20
1	Shift Premium	Premium	Apr-23​	11	20
1	Locum	Locum	Apr-23​	-	20
1	Regular	Salary	Apr-23​	686	20
1	PTO	Salary	Apr-23​	50	20
1	Premium	Premium	Apr-23​	24	20
1	PRN	Premium	Apr-23​	4	20
1	Shift Premium	Premium	Apr-23​	2	20
1	Locum	Locum	Apr-23​	270	20
2	Regular	Salary	Apr-23​	2,553	20
2	PTO	Salary	Apr-23​	404	20
2	Holiday	Salary	Apr-23​	-	20
2	Premium	Premium	Apr-23​	343	20
2	PRN	Premium	Apr-23​	213	20
2	ORIENTATION	Premium	Apr-23​	-	20
2	Shift Premium	Premium	Apr-23​	124	20
2	Locum	Locum	Apr-23​	1,066	20
1	Regular	Salary	May-23​	1,116	20
1	PTO	Salary	May-23​	35	20
1	Premium	Premium	May-23​	97	20
1	PRN	Premium	May-23​	102	20
1	Shift Premium	Premium	May-23​	11	20
1	Locum	Locum	May-23​	-	20
1	Regular	Salary	May-23​	686	20
1	PTO	Salary	May-23​	50	20
1	Premium	Premium	May-23​	24	20
1	PRN	Premium	May-23​	4	20
1	Shift Premium	Premium	May-23​	2	20
1	Locum	Locum	May-23​	270	20
2	Regular	Salary	May-23​	2,553	20
2	PTO	Salary	May-23​	404	20
2	Holiday	Salary	May-23​	-	20
2	Premium	Premium	May-23​	343	20
2	PRN	Premium	May-23​	213	20
2	ORIENTATION	Premium	May-23​	-	20
2	Shift Premium	Premium	May-23​	124	20
2	Locum	Locum	May-23​	1,066	20

 

[arun_eagertolearn]

Hi

Requesting a clarification
When you mean Salary Total to be Total of all--- is it that 2.9 which is shown below Apr-23 should be 0.3+5.6=5.9 or that Grand Total of 2.2 should be the sum of individual entries

Thanks

 

[CONORF]

Both, I need the salary total to be the total of the .3 5.6=5.9. It would be great if the grand total added them as well but I can remove grand totals from the report so that isn't vital. Appreciate your looking at.

 

[arun_eagertolearn]

Hi
Sorry for the late reply
I dont think this would be possible in a pivot table

You may have to remove the subtotal section and do a sumif separately