Change Pivot Table

[dgillz]

OK I have a pivot table with Item Number as the rows and the Year-Month as the columns. I have 4 fields I want summarized in the Pivot Table: Units Sold, Sales $, Cost $ and Gross Margin $. When I add these, the output looks like this:

ITEM 2020-Nov
ABC123 UNITS SALES $ COST $ GROSS MARGIN $
.

Each summary field has added an additional column to the output. I'd like it to look like this:

ITEM 2020-NOV 2020-DEC
ABC123
...UNITS
...SALES $
...COST $
...GROSS MARGIN $
2nd ITEM
...UNITS
...SALES $
...COST $
...GROSS MARGIN $
...
_
Is there a way to do this? I'm kind of at a loss here. Any insight appreciated.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[SkipVought]

How about a few representative rows from your source table to be able to summarize?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Well I couldn't wait for your data, so I mocked up my own.

First tried a query, but TILT, ended up with mixed data types in the pivot output: that is integers and currency

So I went back to an old trick, just outputting the Items and multiple categories as here...
[pre]
Item Category
ABC123 COST
ABC123 GROSS_MARGIN
ABC123 SALES
ABC123 UNITS
BCD234 COST
BCD234 GROSS_MARGIN
BCD234 SALES
BCD234 UNITS
QWE765 COST
QWE765 GROSS_MARGIN
QWE765 SALES
QWE765 UNITS
XYZ098 COST
XYZ098 GROSS_MARGIN
XYZ098 SALES
XYZ098 UNITS
[/pre]
...and using this as a basis for the pivot, albeit a manual pivot--not an Excel PivotTable.

This means adding additional columns having an aggregation formula to sum the row/column values of the pivot.

So the additional columns to manually add will be the pseudo-date columns representing the year-month, which looks like this...
[pre]
Item Category 2020-11 2020-12 2021-01
ABC123 COST
ABC123 GROSS_MARGIN
ABC123 SALES
ABC123 UNITS
BCD234 COST
BCD234 GROSS_MARGIN
BCD234 SALES
BCD234 UNITS
QWE765 COST
QWE765 GROSS_MARGIN
QWE765 SALES
QWE765 UNITS
XYZ098 COST
XYZ098 GROSS_MARGIN
XYZ098 SALES
XYZ098 UNITS
[/pre]

However, these are interpreted as Heading values and not dates (which are really formatted NUMBERS), so it simply complicates the formula as displayed below...
[tt]
C2: =SUMPRODUCT((tDATA[Item]=[@Item])*(tDATA[Date]>=DATE(LEFT(C$1,4),RIGHT(C$1,2),1))*((tDATA[Date]<DATE(LEFT(D$1,4),RIGHT(D$1,2),1))*(INDIRECT([@Category]))))
[/tt]
...where tDATA is the source data in the DATA tab and @Item and @Categoty refer to the respective values within the pivot.

As row are added or deleted in the source tDATA table the pivot can be refreshed to reflect additional rows and the formulas will calculate the appropriate values.

Caveat: you will need one additional date column beyond your latest month to accommodate all your dates.

combo may be able to give you a Power Query solution. I'm not that adept.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

BTW, my source data...
[pre]
Item Date UNITS COST $ SALES $ GROSS_MARGIN $
ABC123 11/12/20 50 $456 $876 $420
ABC123 11/23/20 75 $654 $1,234 $580
ABC123 12/13/20 25 $234 $456 $222
BCD234 11/2/20 10 $123 $234 $111
BCD234 12/3/20 15 $213 $435 $222
XYZ098 11/3/20 22 $333 $555 $222
XYZ098 12/1/20 33 $444 $666 $222
QWE765 12/4/20 55 $777 $999 $222
[/pre]

My results...
[pre]
Item Category 2020-11 2020-12 2021-01
ABC123 COST 1110 234
ABC123 GROSS_MARGIN 1000 222
ABC123 SALES 2110 456
ABC123 UNITS 125 25
BCD234 COST 123 213
BCD234 GROSS_MARGIN 111 222
BCD234 SALES 234 435
BCD234 UNITS 10 15
QWE765 COST 0 777
QWE765 GROSS_MARGIN 0 222
QWE765 SALES 0 999
QWE765 UNITS 0 55
XYZ098 COST 333 444
XYZ098 GROSS_MARGIN 222 222
XYZ098 SALES 555 666
XYZ098 UNITS 22 33
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein