Customer LTV

[jackj]

I am struggling with this and could use some help in solving the below problem:

I am looking to create a calculated column that totals the cumulative lifetime sales associated with each customer ID, and allows for me to use that total in other visuals.  For example, if I want to see the group of customer IDs who visited during last month, I would be able to filter down the visual to those IDs who appeared last month, and show me those individuals' cumulative total sales (not for the month, but for their entire lifetime).   I do not want any external filter to adjust the value of their total lifetime sales. I basically want this value to stick with the customer ID in any other table, matrix, etc. that I run.  Any help would be appreciated.  Thanks!!

[amitchandak]

@jackj , With help from Date table try measure like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('date'),'date'[date] <=max('date'[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,Month)))

[jackj]

Thank you!  My only issue is that I would like it to always ignore any other filters being applied.  So, for example, if I have a table showing User ID's that were here at any given point in time, I want it to always show their total Cumm Sales regardless of the time period selected in the filter.  Does that make sense?

If I place the following table visuals I get different results:

If I filter the table by date = last 1 calendar weekIf I remove the filter. This shows the true lifetime sales. This is the value I always want to see associated with UserID 379449 in any filter context.

In the first screen capture, I am filtering dates by the last calendar week to see users who placed an order in that week.  In the second screen capture, I removed the filter.  Essentially, when I see a list of users who placed orders in the last week, I want to always see their total Cumm Sales - in the case of UserID 379449, it should always be $494.00, regardless of filter context.

Thank you again for the quick reply!

[v-yingjl · Accepted solution]

Hi @jackj ,

If you want the table measure do not affect by the slicer, you can use 'Edit Interaction' to disable the interaction between the table and the slicer.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.