Solved Pivot Table Summary Count minus Total Count (2 Viewers)

sxschech

Registered User.
Local time
Today, 06:09
Joined
Mar 2, 2010
Messages
801
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))

PivotDaily.PNG
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,548
Can you also post a sample Excel file?
 

sxschech

Registered User.
Local time
Today, 06:09
Joined
Mar 2, 2010
Messages
801
Here is a sample. Cleared out data that is not needed for summary rather than obfuscating. Truncated lead and discipline.
 

Attachments

  • DailySummary_SubmittalsOverdueDataLeadChart_20240506-DEMO.zip
    16.8 KB · Views: 19

theDBguy

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,548
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:
1715647215496.png
 

sxschech

Registered User.
Local time
Today, 06:09
Joined
Mar 2, 2010
Messages
801
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

PivotDailyFromZip.PNG
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,548
Hi. Thanks for clarifying. I'll take another look when I get back in front of a computer.

Sent from phone...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,548
Okay, not sure if this is the best answer, but I hope it helps...
 

Attachments

  • DailySummary_SubmittalsOverdueDataLeadChart_20240506-DEMO.zip
    19.5 KB · Views: 11

sxschech

Registered User.
Local time
Today, 06:09
Joined
Mar 2, 2010
Messages
801
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’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,548
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

Registered User.
Local time
Today, 06:09
Joined
Mar 2, 2010
Messages
801
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

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,548
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

Registered User.
Local time
Today, 06:09
Joined
Mar 2, 2010
Messages
801
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

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,548
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

Registered User.
Local time
Today, 06:09
Joined
Mar 2, 2010
Messages
801
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.


Thanks again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,548
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.


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!
 

Users who are viewing this thread

Top Bottom