Reply
GonzaloB
Helper I
Helper I
Partially syndicated - Outbound

Replicate result on Matrix to Chart

Hi guys,

 

I'm trying to build al CLV model and for that I have a calendar table which relates to customer acquisition cost on one side (monthly), customer table which in turn relates to sales tables (fact table)

Gboody_0-1619518942446.png

Now I've managed to do quite a few things but my manager wants me to replicate this chart and I could really use some help.

Gboody_1-1619518980777.png

So far I've managed to show something on a matrix using calculates, but this is far from ideal. 

Gboody_2-1619519030246.png

 

The measures involved are:

Cohort year (year of customer creation date, I'm using a filter from rolling calendar)

LTV (sum of revenue from sales)

Acquisition cost (monthly)

 

I managed to create a calculated column inside the fact table (I know this is also far from ideal) and thought that that might save me, but alas it didn't. This calculated columns calcualts the days between when the order was placed and the customer was created, giving me 'maturity days'

 

I was hoping to drop maturity days as axis, calendar.year as legend and (sum of revenue / cost of acquisition) as values. but it doesn't seem right. I think I'm dividing every sales revenue by the monthly customer acquisition cost, but I don't know how to fix this; it works on a table it does not work on the chart.

Gboody_3-1619519393777.png

 

I hope this made sense, please let me know if you need more information and thank you for any assistance you can provide.

 

I'm afraid I can't understand M and consider myself beginner/intermediate when using DAX.

 

The fact is that on the chart I should be seeing the cumulative sales revenue divided by the initial cohort acquisition cost, hence the ltv/cac should increase as you move further on the axis 

 

Thanks

 

 

 

 

 

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

Syndicated - Outbound

Hi @GonzaloB ,

 

May I ask what is wrong? Are you saying that the data is wrong, or the style of the chart is wrong?

 

Please provide me with more detailed information about your tables, it would be great if you could share a virtual sample data.

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Syndicated - Outbound

Hi,

 

Apologies for the confusion allow me to try and clarify.

Rolling Calendar: simple has very date and I connect through this

 

Acquisition Cost: monthly spent for capturing new customers

 

Customer table: Has customer creation date as well as details 

 

Sales table: every sale the company experienced as well as the Customer who made the purchase

 

 

 

I need a chart that shows the accumulated revenue per customer cohort divided by the cohort acquisition cost.

 

Basically, its Output 1 Divided by Output 2

Syndicated - Outbound

I really wished the reply window was bigger, and for some reason I cannot paste formated tables or add the attached excel file... something along the lines of HTML...

Please excuse me for posting like this:


Output 1 - Acquisition Cost per Customer cluster
Customer Cohort Acquisition cost <30d Acquisition cost <90d Acquisition cost <1000d
1/06/2021 171,667 171,667 171,667
1/10/2020 156,667 156,667 156,667
1/11/2020 171,667 171,667 171,667

Output 2
Revenue per Customer Cluster Revenue cost <30d Revenue cost <90d Revenue cost <1000d
1/06/2021 168 168 633
1/10/2020 975 975 1360
1/11/2020 115 206 206

Expected Output
Cust Cohort Payoff < 12months 12<Payoff < 24months 24<Payoff < 36months
Jun-20 0.00098 0.00098 0.00369
Sep-20 0.00622 0.00622 0.00868
Nov-20 0.00067 0.00120 0.00120




Sample tables
Acquisition cost
Date Acquisition Cost New Customers CAC
1/06/2021 $ 171,667 415 $ 414
1/07/2020 $ 120,000 376 $ 319
1/08/2020 $ 110,000 341 $ 323
1/09/2020 $ 150,000 446 $ 336
1/10/2020 $ 156,667 364 $ 430
1/11/2020 $ 171,667 415 $ 414

Customer
Creation Date Customer ID Customer details
3/06/2020 1 v
8/06/2020 2 x
2/10/2020 3 y
5/11/2020 4 z

Rolling Calendar
Date Day Month Year
1/07/2020 1 7 2020
2/07/2020 2 7 2020
2/07/2020 2 7 2020
3/07/2020 3 7 2020
3/07/2020 3 7 2020
4/07/2020 4 7 2020

Sales
Date Order ID Customer ID SKU Qty Price Revenue Customer creation Order Maturity (days)
2/07/2020 1114522 1 A 5 5 25 3/06/2020 29
2/07/2020 1114522 1 B 4 13 52 3/06/2020 29
2/07/2020 1114522 1 C 3 22 66 3/06/2020 29
2/07/2020 1114523 2 A 5 5 25 8/06/2020 24
30/10/2020 1114524 3 B 75 13 975 2/10/2020 28
1/12/2020 1114525 4 C 4 22 88 5/11/2020 26
1/12/2020 1114525 4 A 1 5 5 5/11/2020 26
1/12/2020 1114525 4 C 1 22 22 5/11/2020 26
1/12/2020 1114526 1 A 1 5 5 3/06/2020 181
1/12/2020 1114526 1 B 2 13 26 3/06/2020 181
2/02/2021 1114527 2 A 4 5 20 8/06/2020 239
2/02/2021 1114528 1 A 5 5 25 3/06/2020 244
2/02/2021 1114528 1 B 2 13 26 3/06/2020 244
2/02/2021 1114528 1 C 4 22 88 3/06/2020 244
2/02/2021 1114528 1 D 5 55 275 3/06/2020 244
2/02/2021 1114529 3 D 7 55 385 2/10/2020 123
2/02/2021 1114530 4 B 7 13 91 5/11/2020 89

 

Syndicated - Outbound

I attached the pictures for clarity.

Expected output.pngCustomer.pngSales.pngRolling Calendar.pngAcq Cost.png

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric Community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)