7 Replies Latest reply on Dec 27, 2016 6:18 AM by Emre Kaya

LoD Clarificaiton Customer-Sales

Hello,

I have two tables customer([Customer ID]  ,CaC, name,..etc) and sales(order date, order amount, product id and [Customer ID] ), joined together  on [Customer ID] . In customer table I have measure called Customer Acquisition Cost(CaC) which is a measure which gives monetary amount to get this particular customer. When I try to calculate total marketing costs per year,

I have used:

{FIXED  [Customer ID] :AVG([CAC])}

to create calculated field and used it  with order dates.

However it gives wrong total cost. It includes all customerids, which appeared on sales table.

It must be very easy to just unique customers and add CaCs to find marketing costs but I have not achieved.

Any recommendations?

Thank you

Edit: My business logic is like this. I have cac for every customer and I am trying to calculate Marketing cost for all years. Please note that customer has join date so marketing cost is associated with it.

IN simple terms, I try to calculate operating profit for every year with total marketing cost(should be derived from CaCs

• 1. Re: LoD Clarificaiton Customer-Sales

Hello Emre,

The calculated field you provide itself is OK, but it depends on how you use it on your view.

For example,

in this view, SUM(Calculatoin1) summerizes the CAC of the customers who have made order in each year.

So could you provide a sample workbook for further advise?

Regards.

• 2. Re: LoD Clarificaiton Customer-Sales

Hi Emre,

Can you elaborate your business question? I will also suggest you to provide the sample data (Dummy preferable) in order to visualize in the best possible way. Would you like to see the CAC at the year level or at the Customer level? I would also suggest you to provide the sample calculation for the one of the cases in excel which will provide better clarity and we can validate the output at our end.

If you calculate the CAC in above way you will get the same customer for the all the year along with the avg CAC. what I think is that Customers are acquired in any one of the business year and later they are retained. I think you should be only worried about the new customer for each and every year and not the all customers since you have acquired few customers for the year.

I hope this helps in someway. Let us know clear business logic. Whatever mentioned above is just my view point and I am no expert.

Thanks and Regards,

Ashish Chaudhari

• 3. Re: LoD Clarificaiton Customer-Sales

Hello Ashish,

I have included worksheet, any recommendation would be perfect.

thanks

Emre

• 4. Re: LoD Clarificaiton Customer-Sales

Hello Lei,

I have included worksheet, any recommendation would be perfect.

thanks

Emre

• 5. Re: LoD Clarificaiton Customer-Sales

Hi Emre!

Probably you need to join at Customer ID=Customer ID  and Join Date=Order Date.

If it does not give you what you are looking for then please share the required output value. It would be great help

Regards,

Sireesha.

• 6. Re: LoD Clarificaiton Customer-Sales

Hello Emre,

Please have a try as follows,

CAC2:

IIF(YEAR([Join Date])=YEAR([Order Date]),[CAC],0)

Marketing Total Cost2:

{INCLUDE [Customer ID] :MAX([CAC2])}

The difference of two calculations,

Finally, the result is,

Regards

• 7. Re: LoD Clarificaiton Customer-Sales

Perfect,thats it. thank you very much !