Forum Discussion

lekkerbek's avatar
lekkerbek
Helper IV
10 years ago
Solved

Revenue per customer

Hi, I'm trying to write a DAX measure where it calculates the average revenue per customer, but I'm getting nowhere :-)   I have 3 tables: - date table - sales table - visitors and buying custom...
Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

    lekkerbek Depends what filters you are going to use. Correct me if is a simple approach but average can calculated if devide revenue by customers. 

    This is mostly if you try to filter by dates.

     

     

     

    Avg Revenue =

VAR  Customers = SUM(Visitors[Buyers])
VAR Revenue = SUM ( Sales[Revenue])

Return

DIVIDE ( Revenue ; customers )

    If you need to filter by other columns like Region that needs another approach

Vvelarde-altern's avatar
Vvelarde-altern
New Member
10 years ago

hi lekkerbek

 

use this dax:

 

AverageRevenueperCustomer =
AVERAGEX (
    SUMMARIZE (
        'Sales-Revenue';
        'Sales-Revenue'[Sales];
        "AverageRevperCustomer"; DIVIDE (
            CALCULATE (
                SUM ( 'Sales-Revenue'[Revenue] );
                FILTER ( 'Sales-Revenue'; 'Sales-Revenue'[Sales] = MAX ( Date[Date] ) )
            );
            CALCULATE (
                SUM ( 'Visitors'[Buyers] );
                FILTER ( 'Visitors'; Visitors[Date] = MAX ( Date[Date] ) )
            )
        )
    );
    [AverageRevperCustomer]
)
  • lekkerbek's avatar
    lekkerbek
    Helper IV
    10 years ago

    Thanks for your reply. It does not work right now, but will have a look.  I think it's a step in the right direction. 

    • a_mixed_life's avatar
      a_mixed_life
      Resolver I
      10 years ago

      I use the AVERAGEX DAX formula to calculate my average against another measurement of SUM('Sales'[Revenue])

      For me to get around to ensure it's average by customer, here's what I had to do. But this depends on your tables.

       

      Total = SUM('Sales'[Revenue])

       

      then created another measure

       

      Average = AVERAGEX('Date Table',[Total])

       

      Hope that helps.