Forum Discussion
Helper IVRevenue 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 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.
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
7 Replies
- Vvelarde
Community Champion
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]
) - 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]
) 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] )
lekkerbekThanks 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
Resolver I
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.
- konstantinos
Memorable Member
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!
