cancel
Showing results for
Did you mean:
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

 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 6
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.

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
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

## 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.

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 =
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
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

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

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!

Announcements

#### 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!

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

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 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

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

Top Solution Authors
Top Kudoed Authors