Pivot Table - Field Value is 1

Contributor

Hi All, 

 

Can someone point me in the direction to resolve this? I need the value field to match the numbers in the LPH column but it's defaulting to a "1" in the pivot table.  Would a formula that its driven from be affecting it?

 

image002 (1).png

image001 (8).png

7 Replies

@Bahama85 

Click anywhere in the pivottable.

Click on the Count of LPH field in the Values area of the PivotTable Fields task pane.

Select Value Field Settings from the drop down menu.

Change the summary function from Count to Sum.

Click OK.

@Hans Vogelaar 

 

Hi Hans, tried that before but it was giving me this pop up

 

image003.png

@Bahama85 

Select the LPH column.

Set the number format to General or Number.

On the Data tab of the ribbon, click Text to Columns, then click Finish.

Switch to the pivottable and refresh it.

Can you now set the summary function to Sum?

@Hans Vogelaar 

Unfortunately doing the text to columns is affecting the formula I have in the column. I haven't seen that before.

image001 (9).png

@Bahama85 

Try

 

=IFERROR([@Sum]/[@Hours],0)

@Hans Vogelaar 

 

Unfortunately that didn't work either. https://1drv.ms/x/s!Agmju254hK9cgkGIukISFqkfapby 

@Bahama85 

You have to refresh the pivot table after changing the formula:

S1784.png

S1785.png

S1786.png

S1787.png