Brain Freeze - Need assistance in Thawing!!!

[McDawg81]

I have multiple tables of sales data orgainzed by sales channel. For each row of data, there are over 70 fields with either categorization of the products or data related to the sale - volume, revenue, ad spend, commissions, etc....

Here is my challenge boiled down to two of the tables for example.

Table a is ALL SALES
Table b is RETAIL SALES

How do I generate WHOLESALE SALES given that ALL SALES-RETAIL SALES = WHOLESALE SALES? I've tried the related functions, but I'm obviously doing a lot of something wrong!!

 

[shg]

If there is a unique identifier associated with each sale, you could add a column with a formula in all sales that checks whether it's a retail sale. Filter on that.

 

[McDawg81]

ALL SALES is just that - a table of all of the sales data without any indication of market channel. The other tables are all subsets of ALL SALES, say RETAIL, C-STORE, BIG BOX, etc. There are multiple cases where I don't have the data for a specific channel, but I know that TABLE A - TABLE B = that sales channel. I need to do the math on all of the fields.

 

[McDawg81]

anyone???

 

[McDawg81]

Beuller???????

 

[shg]

If there's nothing (e.g., a transaction ID) that enables you to identify which 'all sales' are 'retail sales,' how do you plan to exclude the one from the other?

 

[McDawg81]

That is my question. Is there a way, using POWERPIVOT, to subtract one table from another for all value fields?

 

[shg]

If all you need to do is subtract the fields, why not just total the columns on each sheet and then subtract one from the other?

 

[McDawg81]

Two tables like the one below. One is Total Sales, the other is just Retail Sales. Wholesale would be the difference. How do I generate the Wholesale Pivot in Powerpivot?

[TABLE="width: 784"]
<TBODY>[TR]
[TD]Item[/TD]
[TD]Material[/TD]
[TD]Color[/TD]
[TD]Units Sold[/TD]
[TD]Revenue[/TD]
[TD]Advertising[/TD]
[TD]Coupons[/TD]
[TD]Freight[/TD]
[TD]Commissions[/TD]
[TD]Net Price[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Cotton[/TD]
[TD]Blue[/TD]
[TD="align: right"]125[/TD]
[TD] $ 3,748.75[/TD]
[TD] $ 187.50[/TD]
[TD] $ 625.00[/TD]
[TD] $ 31.25[/TD]
[TD] $ 74.98[/TD]
[TD] $ 2,830.03[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Poly[/TD]
[TD]White[/TD]
[TD="align: right"]200[/TD]
[TD] $ 5,998.00[/TD]
[TD] $ 300.00[/TD]
[TD] $ 1,000.00[/TD]
[TD] $ 50.00[/TD]
[TD] $ 119.96[/TD]
[TD] $ 4,528.04[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Silk[/TD]
[TD]Grey[/TD]
[TD="align: right"]118[/TD]
[TD] $ 3,538.82[/TD]
[TD] $ 177.00[/TD]
[TD] $ 590.00[/TD]
[TD] $ 29.50[/TD]
[TD] $ 70.78[/TD]
[TD] $ 2,671.54[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Cotton[/TD]
[TD]White[/TD]
[TD="align: right"]301[/TD]
[TD] $ 9,026.99[/TD]
[TD] $ 451.50[/TD]
[TD] $ 1,505.00[/TD]
[TD] $ 75.25[/TD]
[TD] $ 180.54[/TD]
[TD] $ 6,814.70[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Poly[/TD]
[TD]Grey[/TD]
[TD="align: right"]117[/TD]
[TD] $ 3,508.83[/TD]
[TD] $ 175.50[/TD]
[TD] $ 585.00[/TD]
[TD] $ 29.25[/TD]
[TD] $ 70.18[/TD]
[TD] $ 2,648.90[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Silk[/TD]
[TD]Pink[/TD]
[TD="align: right"]85[/TD]
[TD] $ 2,549.15[/TD]
[TD] $ 127.50[/TD]
[TD] $ 425.00[/TD]
[TD] $ 21.25[/TD]
[TD] $ 50.98[/TD]
[TD] $ 1,924.42[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Cotton[/TD]
[TD]Grey[/TD]
[TD="align: right"]14[/TD]
[TD] $ 419.86[/TD]
[TD] $ 21.00[/TD]
[TD] $ 70.00[/TD]
[TD] $ 3.50[/TD]
[TD] $ 8.40[/TD]
[TD] $ 316.96[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Poly[/TD]
[TD]Pink[/TD]
[TD="align: right"]32[/TD]
[TD] $ 959.68[/TD]
[TD] $ 48.00[/TD]
[TD] $ 160.00[/TD]
[TD] $ 8.00[/TD]
[TD] $ 19.19[/TD]
[TD] $ 724.49[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[McDawg81]

Anyone have any ideas? I'm stumped. I know I can do this in a klugy fashion, just hoping not to have to make all the helper columns in order to pull this off.