Forum Discussion

John12345's avatar
John12345
New Member
4 years ago

Improve Formula Efficiency on Measure with Calculated Table

Hi,
I'm having a problem with a measure I'm using in a report on customer LTV. The measure works as expected, but when I publish it online I get a 'visual exceeds available resources' error, so I'm looking for ways to make it more efficient.
 
Formula below:
Monthly Cohort Revenue =
var cpw = SELECTEDVALUE(wp_meal_orders[Cohort Period Week])
var jcm = SELECTEDVALUE(wp_meal_orders[Customer Join Cohort Month])
var tabler = SUMMARIZE(filter(ALLSELECTED(wp_meal_orders),wp_meal_orders[Customer Join Cohort Month]=jcm),wp_meal_orders[Customer Join Cohort Month],wp_meal_orders[Cohort Period Week],wp_meal_orders[Customer Weeks Since Join])
var newtable = SUMMARIZE(filter(tabler,isblank([Cohort Period Week])=false && isblank([Customer Weeks Since Join])=false),wp_meal_orders[Cohort Period Week],wp_meal_orders[Customer Join Cohort Month])
var tester = calculate(sumx(addcolumns(newtable,"average",divide(calculate([Total Revenue],wp_meal_orders[Customer Weeks Since Join]>EARLIER(wp_meal_orders[Cohort Period Week])),[Unique Customers for Cohort Group_Month for Weeks])),[average]),filter(ALLSELECTED(wp_meal_orders),[Cohort Period Week]<=cpw && wp_meal_orders[Customer Join Cohort Month]=jcm))
var tr = [Total Revenue]
return
if(tr=blank(),blank(),tester)
 
 And this is what the result looks like:

 

 

The formula is designed to take the customer revenue per possible user (not just active) in a given cohort, and aggregate them. So for week 1 in the chart, it would be total revenue week 0 for cohort/total members of cohort + total revenue week 1 for cohort/total members of cohort who joined at least 1 week ago.

 

I'm wondering if there's a more efficient way to build the table variables I'm using that won't overuse the available resources.

 

Thanks!

5 Replies

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi:

    Are you asking about LTV or Monthly Revenue? 

    to best answer your question ,some more info would be helpful (example file) pictures of data model and table columns.

    Normally LifeTime Value for Total Revenue would be:

        = CALCULATE([Total Revenue], FILTER(ALL(Dates[Date]), Dates[Date] <= MAX(Dates[Date]))

  • John12345's avatar
    John12345
    New Member
    4 years ago

    Thanks Whitewater,

     

    I'm trying to calculate LTV by customer, but the difficulty is we want to use monthly cohorts, but the column headers would be weeks. In most circumstances, I could just do total revenue/customers, and that would solve it, however in this case, customers in the same cohort might have joined weeks apart, and I want to account for that, by only putting customers in the denominator who actually could have ordered in a given week, rather than all customers in the cohort.

     

    Here's an excel that hopefully does a better job of explaining.

     

    https://docs.google.com/spreadsheets/d/1yRtV9eZP8Txe3WFkFpD5ZWUbxGjmtr6X6lSwnFnL69k/edit?usp=sharing 

     

    • Whitewater100's avatar
      Whitewater100
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi John:

      Thanks for the update. I'm leaving for a trip in a few minutes. If not solved by Sunday, can you remind me to check it out? Thank you

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi John:

    Thank you for providing some more info. I'm still not entirely understanding.

    How do you assign customers to their cohort group? Is this group dynamic?

    If you had some example tables with data and expected results(and pitfalls) it could help. I'm sorry for not totally tracking on how you want this analysis.

    Have a goood rest of the day!