cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MikeO Helper I
Helper I

Allocating 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

 

LocationCustomerSales AmountSourceCreativeRun Date
TexasBob Smith1000A21/15/2018
MontanaJoe Dirt800B21/2/2018
ColoradoRichard Jones390C31/20/2018
KentuckyGene Simmons850D31/3/2018
CaliforniaArbor Reed900E1

1/10/2018

 

 

Marketing Table

 

SourceCreativeCostRun DateEnd Date
A15001/1/20181/15/2018
A23001/15/20181/27/2018
A33001/27/20182/28/2018
B24001/2/20181/10/2018
B210001/10/20182/28/2018
C12001/1/20181/20/2018
C33501/20/20181/23/2018
C23501/23/20181/30/2018
C15001/30/20182/28/2018
D34001/3/20181/18/2018
D28001/18/20182/28/2018
E13001/2/20181/10/2018
E12001/10/20181/15/2018
E35001/15/20182/28/2018

 

 

Allocation Table

 

SourceLocationAllocation Percentage
ATexas85%
AMontana15%
BColorado20%
BKentucky80%
CCalifornia35%
CFlorida35%
CKentucky30%
DTexas70%
DColorado30%
EMontana20%
EFlorida20%
EKentucky20%
ETexas20%
EColorado20%

 

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 6
Community Support
Community Support

Re: Allocating Cost Per Location for ROI Calculations

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.

14.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
MikeO Helper I
Helper I

Re: Allocating Cost Per Location for ROI Calculations

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.

Community Support
Community Support

Re: Allocating Cost Per Location for ROI Calculations

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.

14.PNG

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
MikeO Helper I
Helper I

Re: Allocating Cost Per Location for ROI Calculations

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

MikeO Helper I
Helper I

Re: Allocating Cost Per Location for ROI Calculations

@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],
","
)

 

Marketing Cost Location Formula.PNG

MikeO Helper I
Helper I

Re: Allocating Cost Per Location for ROI Calculations

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!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors