Forum Discussion

RyanNewportJP's avatar
RyanNewportJP
Frequent Visitor
1 year ago
Solved

Average LTV Measure

Hi all,

 

I'm trying to create a measure that returns the average LTV for all customers in table.

The table has individual product purchases for rows, so I want to add up all the instances of a product purchased's sales for that customer. 

CustomerIDSales 
14158
217155
217155
217155
217155
218018
2520
35709
35709
317655
317655

 

If I was doing this on Excel, I would create a SUMIF looking at the CustomerID column, matching CustomerID with the ID I want to sum first, then select the Sales column. Drag down to SUMIF all customers, then just select them all and look at the average on the bottom of Excel. 

 

I found a couple of CALCULATE and SUMX formulas online that check for some specific expression, but the expression I've tried isn't working when I'm trying to recall CustomerID. 

 

Ex.

LTV = SUMX(FILTER(Table,[CustomerID]=[CustomerID]),[Sales])
or
LTV = CALCULATE(SUM([Sales]),[CustomerID]=[CustomerID])
 
Then I would just do Average(LTV).
 
The above formulas are either returning the total nubmer of Sales or returning the individual instance of Sales for one line item.
 
I'm fresh to Power BI, and frankly overwhlemed by all the tutorials. I feel like this should be simple, but my brian just won't wrap around it...
 
Any help would be appreciated! 
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    RyanNewportJP First create a measure for Total Sales per Customer

     

    TotalSalesPerCustomer =
    SUMX(
    VALUES(Table[CustomerID]),
    CALCULATE(SUM(Table[Sales]))
    )

     

    Then create a measure for Average LTV

    AverageLTV =
    AVERAGEX(
    VALUES(Table[CustomerID]),
    [TotalSalesPerCustomer]
    )

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    RyanNewportJP First create a measure for Total Sales per Customer

     

    TotalSalesPerCustomer =
    SUMX(
    VALUES(Table[CustomerID]),
    CALCULATE(SUM(Table[Sales]))
    )

     

    Then create a measure for Average LTV

    AverageLTV =
    AVERAGEX(
    VALUES(Table[CustomerID]),
    [TotalSalesPerCustomer]
    )

    • RyanNewportJP's avatar
      RyanNewportJP
      Frequent Visitor
      1 year ago

      Thank you very much! This worked!
      Can I ask why it works?

  • CamdenWalker's avatar
    CamdenWalker
    New Member
    1 year ago

    It helped me also, Thank you so much.

    Spoiler
    To calculate the average customer lifetime value (LTV) in Power BI, you'll want to first summarize the sales for each customer by grouping the data based on the CustomerID. Once you have the total sales per customer, you can then calculate the average of these total sales across all customers. This approach ensures you are considering each customer's cumulative spending before calculating the overall average. I had a tough essay assignment on a topic I wasn’t familiar with, so I decided to seek professional help. After researching online, I came across Academized which we can find here https://academized.com/custom-essay and decided to try their custom essay service. I was impressed by how fast the process was and how well the essay was written. It was well-structured, coherent, and free from any errors. The writer clearly put a lot of effort into it, and it helped me score better than I expected!