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:
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:
Product Table:
Product Type
Dresses
Mfg Table:
Mfg Code Mfg Name
01 A
02 B
03 C
04 D
Manufacturer Spend
Product weighted
Desired Outcome
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 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:
Products | Manufacturer Item # |
Caps | A A-12 |
Caps | C C-63 |
Gowns | B B-15 |
Gowns | C C-21 |
Gowns | D D-03 |
Shoes | A A-14 |
Shoes | B B-22 |
Shoes | C C-22 |
Shoes | D D-22 |
Dresses | C C-35 |
Dresses | D D-35 |
Product Table:
Product Type
Caps |
Gowns |
Shoes |
Mfg Table:
Mfg Code Mfg Name
01 A
02 B
03 C
04 D
Manufacturer Spend
Manufacturers | Mfg Specific spend |
A | 625 |
B | 741 |
C | 525 |
D | 112 |
Product weighted
Products | Manufacturer | Weighted Spend |
Caps | A | 62.5 |
Caps | C | 62.5 |
Gowns | B | 76.66 |
Gowns | C | 76.66 |
Gowns | D | 76.66 |
Shoes | A | 137.75 |
Shoes | B | 137.75 |
Shoes | C | 137.75 |
Shoes | D | 137.75 |
Dresses | C | 160.5 |
Dresses | D | 160.5 |
Desired Outcome
Manufacturer | Total Spend |
A | 825.25 |
B | 955.41 |
C | 962.41 |
D | 486.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?