Revenue per customer

[lekkerbek]

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 customers table (just numbers of visitors and buyers each day)

Sales table is linked to the date table and the visitors and buying customers table is also linked to the date table.

I had hoped the following code would do the trick, but it does not:

Average revenue per customer = CALCULATE(sum(Sales[Revenue]);Date[Date]) / CALCULATE(sum(Visitors[Buyers]);Date[Date])

Does anybody have a clue?

Thanks in advance.

[konstantinos · Accepted solution]

@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

[lekkerbek]

Thanks that worked excellent!

[Vvelarde-altern]

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]

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]

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.

[Vvelarde]

AverageRevenueperCustomer =
AVERAGEX (
    SUMMARIZE (
        'Sales-Revenue';
        'Sales-Revenue'[Sales];
        "AverageRevperCustomer"; DIVIDE (
            CALCULATE (
                SUM ( 'Sales-Revenue'[Revenue] );
                FILTER ( 'Sales-Revenue'; 'Sales-Revenue'[Sales] = MAX ( Calendario[Date] ) )
            );
            CALCULATE (
                SUM ( 'Visitors'[Buyers] );
                FILTER ( 'Visitors'; Visitors[Date] = MAX ( Calendario[Date] ) )
            )
        )
    );
    [AverageRevperCustomer]
)

[Vvelarde]

hi @lekkerbek

Use this to try to solve it:

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]
)