Pivot Table - Field Value is 1

Copper 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

8 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

Not sure if you ever found a resolution for this, but I was having a similar issue and was able to fix it. My problem was the data in the table I used to make the pivot table was formatted as text rather than numbers when I imported the table from the web. To fix this, if you type "1" into any empty cell in your worksheet and then copy that cell after you've hit enter, you can highlight the group of cells you wish to change into numbers and (under the home tab) click paste and then paste special. Once the menu has popped up, select the multiply option and hit ok. This will multiply all highlighted values by 1 and change them to numbers. You'll know it worked if the alignment of all the newly formatted cells changes from left to right aligned. Hope this helps if you still need it.