Forum Discussion
Helper IAllocating Cost Per Location for ROI Calculations
I think I may be overcomplicating this issue as I have already built two new tables (a date table and a source table for purposes of building relationships between the three tables discussed below) in an attempt to come up with a way to allocate my cost per location for determining ROI, but I can't figure out how to calculate cost per location using either a Measure or a Calculated Column. To start with, I have three tables: 1) A customer sales table that shows every sale and has a marketing source tied to it; 2) a marketing table that shows every source of marketing, the name and cost of every creative, the run date and the end date of each creative; and 3) an allocation table that shows the percentage allocation each location should bear for for a creative based on the source. I have provided these below:
Customer Sales Table
| Location | Customer | Sales Amount | Source | Creative | Run Date |
| Texas | Bob Smith | 1000 | A | 2 | 1/15/2018 |
| Montana | Joe Dirt | 800 | B | 2 | 1/2/2018 |
| Colorado | Richard Jones | 390 | C | 3 | 1/20/2018 |
| Kentucky | Gene Simmons | 850 | D | 3 | 1/3/2018 |
| California | Arbor Reed | 900 | E | 1 | 1/10/2018 |
Marketing Table
| Source | Creative | Cost | Run Date | End Date |
| A | 1 | 500 | 1/1/2018 | 1/15/2018 |
| A | 2 | 300 | 1/15/2018 | 1/27/2018 |
| A | 3 | 300 | 1/27/2018 | 2/28/2018 |
| B | 2 | 400 | 1/2/2018 | 1/10/2018 |
| B | 2 | 1000 | 1/10/2018 | 2/28/2018 |
| C | 1 | 200 | 1/1/2018 | 1/20/2018 |
| C | 3 | 350 | 1/20/2018 | 1/23/2018 |
| C | 2 | 350 | 1/23/2018 | 1/30/2018 |
| C | 1 | 500 | 1/30/2018 | 2/28/2018 |
| D | 3 | 400 | 1/3/2018 | 1/18/2018 |
| D | 2 | 800 | 1/18/2018 | 2/28/2018 |
| E | 1 | 300 | 1/2/2018 | 1/10/2018 |
| E | 1 | 200 | 1/10/2018 | 1/15/2018 |
| E | 3 | 500 | 1/15/2018 | 2/28/2018 |
Allocation Table
| Source | Location | Allocation Percentage |
| A | Texas | 85% |
| A | Montana | 15% |
| B | Colorado | 20% |
| B | Kentucky | 80% |
| C | California | 35% |
| C | Florida | 35% |
| C | Kentucky | 30% |
| D | Texas | 70% |
| D | Colorado | 30% |
| E | Montana | 20% |
| E | Florida | 20% |
| E | Kentucky | 20% |
| E | Texas | 20% |
| E | Colorado | 20% |
The need for this came because I am trying to use the Customer Sales table--this was easy as I just created a measure that sums the sales amount column from the Customer Sales table. I tried to allocate cost with the formula below, but the problem I ran into was whenever a creative generated zero sales to any store that should pay for part of a creative, there is no cost allocated to those locations, thus only showing an allocated cost when each location had sales.
Allocated Cost of Creative = DIVIDE((CALCULATE(FIRSTNONBLANK(Allocation Table[Allocation Percentage],1),FILTER(Allocation Table,Allocation Table[Source]=EARLIER(Customer Sales[Source])&&Allocation Table[Location]=EARLIER(Customer Sales[Location])))*Customer Sales[All Location Cost of Creative]),Customer Sales[Number of Sales to Location per Creative])
The All LocationCost of Creative and Number of Sales to Location per Customer were additional columns I have created to create the above formula, but are by no means necessary if there is an easier way to create this formula.
Any help would be appreciated!
6 Replies
HI MikeO,
I test with your data and try to find out the unique location from allocating table and marketing table, but it still contains duplicate records.
Please share more detailed information to help mapping percent and location more accurate.
Regards,
Xiaoxin Sheng
MikeOHi, Anonymous. I have provided a unique location, unique source and a calendar table below:
Unique Location
Location Texas Montana Colorado Kentucky California Florida Unique Source
Source A B C D E Calendar Table
Date 12/1/2017 12/2/2017 12/3/2017 12/4/2017 12/5/2017 12/6/2017 12/7/2017 12/8/2017 12/9/2017 12/10/2017 12/11/2017 12/12/2017 12/13/2017 12/14/2017 12/15/2017 12/16/2017 12/17/2017 12/18/2017 12/19/2017 12/20/2017 12/21/2017 12/22/2017 12/23/2017 12/24/2017 12/25/2017 12/26/2017 12/27/2017 12/28/2017 12/29/2017 12/30/2017 12/31/2017 1/1/2018 1/2/2018 1/3/2018 1/4/2018 1/5/2018 1/6/2018 1/7/2018 1/8/2018 1/9/2018 1/10/2018 1/11/2018 1/12/2018 1/13/2018 1/14/2018 1/15/2018 1/16/2018 1/17/2018 1/18/2018 1/19/2018 1/20/2018 1/21/2018 1/22/2018 1/23/2018 1/24/2018 1/25/2018 1/26/2018 1/27/2018 1/28/2018 1/29/2018 1/30/2018 1/31/2018 2/1/2018 2/2/2018 2/3/2018 2/4/2018 2/5/2018 2/6/2018 2/7/2018 2/8/2018 2/9/2018 2/10/2018 2/11/2018 2/12/2018 2/13/2018 2/14/2018 2/15/2018 2/16/2018 2/17/2018 2/18/2018 2/19/2018 2/20/2018 2/21/2018 2/22/2018 2/23/2018 2/24/2018 2/25/2018 2/26/2018 2/27/2018 2/28/2018 3/1/2018 3/2/2018 3/3/2018 3/4/2018 3/5/2018 3/6/2018 3/7/2018 3/8/2018 3/9/2018 3/10/2018 3/11/2018 3/12/2018 3/13/2018 3/14/2018 3/15/2018 3/16/2018 3/17/2018 3/18/2018 3/19/2018 3/20/2018 3/21/2018 3/22/2018 3/23/2018 3/24/2018 3/25/2018 3/26/2018 3/27/2018 3/28/2018 3/29/2018 3/30/2018 3/31/2018 I hope these help. Thank you for trying to help with this.
HI MikeO,
In fact, I am try to mapping your location column to marketing table for lookup the correspond percentage.
Unfortunately, there are lots of records which can't mapping correctly.I'd like some detail data or other conditions who can help to remove duplicate location result.
Calculate column formula I used to mapping location:
Location = VAR temp = ADDCOLUMNS ( Marketing, "Location2", LOOKUPVALUE ( 'Customer Sales'[Location], 'Customer Sales'[Creative], Marketing[Creative], 'Customer Sales'[Source], Marketing[Source] ) ) VAR list = SELECTCOLUMNS ( FILTER ( temp, [Source] = EARLIER ( Marketing[Source] ) ), "Loc", [Location2] ) VAR lookup = LOOKUPVALUE ( 'Customer Sales'[Location], 'Customer Sales'[Creative], Marketing[Creative], 'Customer Sales'[Source], Marketing[Source] ) VAR remian = CONCATENATEX ( FILTER ( ALL ( Allocation ), Allocation[Source] = Marketing[Source] && NOT ( [Location] IN list ) ), [Location], "," ) RETURN IF ( lookup <> BLANK (), lookup, remian )Regards,
Xiaoxin Sheng
