Solved Pivot Table Summary Count minus Total Count

[sxschech]

Have exported access query data to excel. Created a pivot table to count up by Person. Was asked to add cols for days of week and provide count per day along with a col at the end that is the sum of the daily counts minus the original total count. I looked at a few examples on various sites and most were showing how to subtract one col from another. I tried modifying the example to encompass multiple cols and ended up with total of 0 rather than the actual number. As a work around, have added a non pivot col and put standard formula there to get the results. Can this be done within the pivot or is this the best/easiest solution?

This file/pivot will be regenerated as a new file each week from the access export.
I add 5 cols on the detail sheet for the weekdays which are manually marked with their initials by the users entering the data to be counted up by the pivot table

Here is a screen shot of pivot the table with the manually added col shaded in col H. (B4-sum(C4:G4))

 

[theDBguy]

Can you also post a sample Excel file?

 

[sxschech]

Here is a sample. Cleared out data that is not needed for summary rather than obfuscating. Truncated lead and discipline.

 

[theDBguy]

Here is a sample. Cleared out data that is not needed for summary rather than obfuscating. Truncated lead and discipline.

Sorry, but the sample file you provided doesn't match the image you posted. There was only one date in it. In any case, to add the Weekly Total, you could try going to the Ribbon and select the Grand Total button and turn on the Total for the Rows only.

For example:

 

[sxschech]

Since you mentioned the file does not match the image, I downloaded the zip I posted and the file looks fine to me, not sure what changed when you opened it.

Have attached screen shot from that summary page.

Perhaps I didn't explain the issue well? The data in the pivot from Col A through Col G is set up properly per the screen shot. Only modification was to see if the data in Col H could be part of the Pivot table. The data counts in Col C through Col G is not related to the date col in the detail list (Submittal Data tab). I couldn't use the date field to define these counts since the counts are derived after the fact and updated by multiple people, so to get the counts per day, I am counting up the users initials in the Submittal Data tab from Cols I through Col M.
Col B is the total count by person at the start of the week and then the sum of the subsequent data entry is subtracted from that (Col B) to get the Week Ending Total in Col H

 

[theDBguy]

Hi. Thanks for clarifying. I'll take another look when I get back in front of a computer.

Sent from phone...

 

[theDBguy]

Okay, not sure if this is the best answer, but I hope it helps...

 

[sxschech]

I see that you added two cols to the data tab. Tried recreating in my local file by adding the two cols and adjusting the Change PivotTable Data Source Range to include col A through Col O. Not sure how the formula or calculation was performed on the pivot under label Sum of Weekly Total (col H)

 

[theDBguy]

I see that you added two cols to the data tab. Tried recreating in my local file by adding the two cols and adjusting the Change PivotTable Data Source Range to include col A through Col O. Not sure how the formula or calculation was performed on the pivot under label Sum of Weekly Total (col H)

I used a Calculated Field to add the formula to get what you wanted. I tried to do it without adding the extra columns, but calculated fields work well with number columns rather than text ones. There's probably a way to do it without adding those extra columns, but I thought I would give you a quick answer now, which should give me more time to figure out the best way to do it.

 

[sxschech]

Yes, I understood there was a calculated field involved. I haven't used Pivot tables much so not familiar with all its capabilities. My question was how did you create the calculated field in the pivot. I see in the detail there is a formula that counts up the cols =CountA(I2:M2) and a simple count of 1 in the col O. Data Range of pivot is 'Submittal Data'!$A$1:$O$313
How was the field in pivot titled Sum of Weekly Total in Col H derived since I don't see a formula that is calculating the difference between the numbers?

 

[theDBguy]

Yes, I understood there was a calculated field involved. I haven't used Pivot tables much so not familiar with all its capabilities. My question was how did you create the calculated field in the pivot. I see in the detail there is a formula that counts up the cols =CountA(I2:M2) and a simple count of 1 in the col O. Data Range of pivot is 'Submittal Data'!$A$1:$O$313
How was the field in pivot titled Sum of Weekly Total in Col H derived since I don't see a formula that is calculating the difference between the numbers?

Hi. Sorry, I was at work yesterday and didn't have access to the file we were using, so I couldn't tell you what I did. If you open the file and go to the PivotTable Analyze tab on the Ribbon, you can click on the Fields, Items, & Sets button and select Calculated Field. From the dropdown, select Weekly Total to see the expression I used to calculate the extra columns. Hope that helps...

 

[sxschech]

Thanks, I thought I tried looking at the calculated field and it only had the option to add not view or edit existing. Maybe missed a step? Found the formula by clicking on List Formulas. It ended up adding a new tab to the spreadsheet with the information. Seems a bit odd rather than being able to view the information directly.

 

[theDBguy]

Thanks, I thought I tried looking at the calculated field and it only had the option to add not view or edit existing. Maybe missed a step? Found the formula by clicking on List Formulas. It ended up adding a new tab to the spreadsheet with the information. Seems a bit odd rather than being able to view the information directly.

Yup! That's another way of doing it. Good luck with your project!

 

[sxschech]

After a bit more web searching found out how to see the formula. Apparently I misidentified the part in your explanation about where the drop down box is and that is why couldn't see or edit the formula. The video below showed the steps you wrote about in post #11 and once the drop down box is clicked, can select the field, see the formula and modify it.

How to Create Excel Pivot Table Calculated Field Examples

Examples how to create calculated field in pivot table, and remove temporarily or permanently. Video and written step-by-step instructions

www.contextures.com

Thanks again.

 

[theDBguy]

After a bit more web searching found out how to see the formula. Apparently I misidentified the part in your explanation about where the drop down box is and that is why couldn't see or edit the formula. The video below showed the steps you wrote about in post #11 and once the drop down box is clicked, can select the field, see the formula and modify it.

How to Create Excel Pivot Table Calculated Field Examples

Examples how to create calculated field in pivot table, and remove temporarily or permanently. Video and written step-by-step instructions

www.contextures.com

Thanks again.

I still haven't found a way without using extra columns. Please let us know if you do find a way to get rid of them. Good luck!

 

[sxschech]

I still haven't found a way without using extra columns.

Even so, your technique is an improvement to what I started with by trying to put a formula in each cell separate from the actual pivot table.

 

[theDBguy]

Even so, your technique is an improvement to what I started with by trying to put a formula in each cell separate from the actual pivot table.

Update: I'm at work right now, so I can't show you how, but I finally figured out how to add the calculated column without modifying the data source by using Power Pivot and DAX expressions.

Sent from phone...