Allocating Cost Per Location for ROI Calculations

[MikeO]

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!

[v-shex-msft]

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

[MikeO]

Hi, @v-shex-msft.  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.

[v-shex-msft]

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

[MikeO]

Hi @v-shex-msft, was my response helpful?  I now have a column with the name of each location, but I am still not sure how I can put these into a table to allocate my cost per location for determining ROI.  I can use Run Date as a time filter, but how can I get each separate Location in a single column with this new Location column?  As it stands, if I select the new Location column it each row would look something like "Texas,Montana", "Colorado,Kentucky", etc.

Did you have something in mind for how I can now get each location separated in a table?

Thank you!

[MikeO]

@v-shex-msft I believe I found a way to concatenate all of the locations tied to each source with the following formula:

Location Test = CONCATENATEX(
FILTER (
ALL ( 'Allocation Table' ),
'Allocation Table'[Source] = 'Marketing Table'[Source]
),
[Location],
","
)

[MikeO]

Good morning, @v-shex-msft, 

Do the three unique tables I provided in my prior comment help? What it looks like your strategy is to create a calculated column with the name of each location for each creative, perhaps you do not need to filter based on the Customer Sales Table as that table may or may not have a sale from each store.  The Allocation Table contains the name of every location that should be concatenated in the location table.

I don't completely understand your calculated columns formula, if you could explain to me what the formula means I may be able to provide additional help.

Best regards,

Mike Olsen