PDA

View Full Version : [SOLVED] pivot table question



steve400243
05-25-2017, 09:14 AM
Hello, I use a pivot table to show how many customer reference numbers are done in a month. I would like the count column to show just the total? Is this possible? I cannot figure out how to make this happen. Thanks for any help provided.



Reference
Count


Magnabilities
1


79907252685
1


Mechatronics
10


79903088794
1


79904096168
1


79906457350
1


79906925828
1


79904818173 / ASQNSH17040483
1


79908520114
1


79904054639 / FTNVSHS000146568
1


79904054407
1


79905323132
1


79904681852
1


Montana Fly
1


79908133272
1


Olympic Foundry
1


79903047972
1


Totals
13

p45cal
05-25-2017, 10:22 AM
From the little that you give us, lose the subtotals, perhaps lose the column labels that include Magnabilities etc., and collapse the groups, or perhaps have a pivot with nothing in the row and column label areas and just a count of Reference in the Values area.
Supply a file and we can be more explicit.

steve400243
05-25-2017, 10:37 AM
Hello P45cal, Here is the file. Thank you for your help.

p45cal
05-25-2017, 12:00 PM
In the attached, in the vicinity of cell S3 of the Summary sheet there's a small pivot table which gives you the total customer references according to the existing Month slicer choices.
In cell S1 there's a formula giving the same data from your pivot table.
If you select any cell in the reference column of your pivot table and then go to the ribbon, Pivot table tools, Options, Active Field section, and click on Expand/Collapes Entire field you'll see just the subtotals and Grand Total.

BTW, your long formula (to get quarters) of:
=IF(MONTH([@[Comp Date]])=1,[@Year]&"-Q3",IF(OR(MONTH([@[Comp Date]])=3,MONTH([@[Comp Date]])=4,MONTH([@[Comp Date]])=5),[@Year]&"-Q4",IF(OR(MONTH([@[Comp Date]])=6,MONTH([@[Comp Date]])=7,MONTH([@[Comp Date]])=8),[@Year]+1&"-Q1",IF(OR(MONTH([@[Comp Date]])=9,MONTH([@[Comp Date]])=10,MONTH([@[Comp Date]])=11),[@Year]+1&"-Q2",IF(MONTH([@[Comp Date]])=12,[@Year]+1&"-Q3","")))))
can be shortened to:
=[@Year] & "-Q" & CEILING(MOD(MONTH([@[Comp Date]])+6,12)+1,3)/3
and the current formula (for the month) of:
=CHOOSE(MONTH([@[Comp Date]]),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")can be shortened to:
=TEXT([@[Comp Date]],"mmm")

steve400243
05-25-2017, 12:41 PM
Thats great, I knew it had to be in the field selection but just could not find it. Thank you for your time in looking at the file. I really appreciate it. Thanks also for tips on the long formulas, I was going to look at that also. Have a great weekend!!