Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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.
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
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!
@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],
","
)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |