Many to Many nightmare

KCantor

New member
Joined
Apr 4, 2016
Messages
15
Reaction score
0
Points
0
Okay, this one is a tough one for me but is probably very simple for some.

I have two fact tables: Campaigns and Sales. Normally I use one item table as a look up for the sales facts that is based upon item number which associates manufacturer and product category. The campaign fact table has both of those listings but not an item number which means it is a many to many connection which doesn't work too well. In order to calculate totals, I broke the item table into two pieces: Manufacturer and Product. Some campaigns have a manufacturer but not a product and some have a product and not a manufacturer. I would like to use this data to apply a weighted amount to manufacturers for product campaigns that do not have a manufacturer. I have given these unassigned campaigns generic names in order to build a foundation. What I don't know how to do is to bring the two look ups together. I would like to divide the product spend by the number of manufacturers for that product and assign it to the manufacturer to add to manufacturer specific totals.

Sample data:


Campaign Spend:
Campaign
Total Spend
Mfg Name
Product
C Caps
23
C
Caps
D Shoes
15
D
Shoes
A
625
A

B
741
B

Shoes
536
None
Shoes
Dresses
321
None
Dresses
Caps
102
None
Caps
Gowns
230
None
Gowns
C
525
C

D
112
D



Sales Performance:
Item# QTY Amount
A-12 3 12.00
A-32 2 18.00
B-14 1 2.00
C-16 5 20.00
D-15 3 21.00
D-03 7 63.00


Item Table:

ProductsManufacturer Item #
CapsA A-12
CapsC C-63
GownsB B-15
GownsC C-21
GownsD D-03
ShoesA A-14
ShoesB B-22
ShoesC C-22
ShoesD D-22
DressesC C-35
DressesD D-35

Product Table:
Product Type

Caps
Gowns
Shoes
Dresses


Mfg Table:
Mfg Code Mfg Name
01 A
02 B
03 C
04 D

Manufacturer Spend

ManufacturersMfg Specific spend
A625
B741
C525
D112

Product weighted
ProductsManufacturerWeighted Spend
CapsA62.5
CapsC62.5
GownsB76.66
GownsC76.66
GownsD76.66
ShoesA137.75
ShoesB137.75
ShoesC137.75
ShoesD137.75
DressesC160.5
DressesD160.5


Desired Outcome
ManufacturerTotal Spend
A825.25
B955.41
C962.41
D486.91


Is this possible to bring this into PowerQuery through a calculation? Something like if mfg name = "None" then divide total campaign cost by distinct count of the mfgs under product ID else total campaign cost?
 
I'm trying at this point to make sure I even know how to get the right totals, so I took your tables, threw them in a spreadsheet, and tried to figure out the math. Attached is what I have so far, and maybe that will help others chime in. I can get to the Desired Outcome for A and B, but not C and D. Can you check that this is even on the right track and fill in some details?

What's your comfort level with Power Pivot? Power Query is awesome, but the formula you're looking to construct at the end of your post (if mfg name = "None" then divide total campaign cost by distinct count of the mfgs under product ID else total campaign cost) sounds like everyday Power Pivot functionality. Might be easier to go that route if you load your tables to the data model, but I don't know what your end result needs to be.
 

Attachments

  • KCantor.xlsx
    20 KB · Views: 14
Please post your file on the forum. You will get more ( and perhaps better) answers and every member will be able to access it.Thank you
 
Last edited:
@Pecoflyer, It would not allow me to post the file due to size (even though it isn't that large). That is why I posted a dropbox link.
 
I assumed a dropbox file would be just as easy to open. I am attaching a zip file. Any help is appreciated.
 

Attachments

  • test data 2.0.zip
    3.1 MB · Views: 7
Back
Top