Forum Discussion
Revenue PER client -- urgent help needed!
I want to calculate the Average Purchase Value for each of my clients. That is defined as: APV = Total revenue per client/ number of that client's orders. I will then take each average per customer, add it together, and divide that by the number of clients (so this needs to be an actual measure, not just a table I make). I need help trying to get the total number of revenue and total number of orders per client. I have 2 tables (and can create any)
TABLE 1=Transactions
| Transaction ID | UserID | Amount | Date |
| 121212 | 1 | 12 | 1/12/19 |
| 13141415 | 1 | 12 | 2/12/19 |
| 134242523 | 2 | 12 | 1/12/19 |
| 5435354 | 3 | 12 | 1/12/19 |
| 2342432 | 2 | 5 | 2/12/19 |
| 3243252132 | 2 | 5 | 3/12/19 |
I also have a USERS table with a list of unique customer IDs
Again, trying to get a measure or column in the usres table or a new table that gives me the revenue PER client and count of orders PER client
Thank you for your help!!
Anonymous
Try this:
the model:
And the measures:
Sum of Amount = SUM('DataTable'[Amount])Number of Transactions = DISTINCTCOUNT('DataTable'[Transaction ID])Average Purchase Value overall = DIVIDE([Sum of Amount]; [Number of Transactions])Average Purchase value per customer = AVERAGEX('User ID Table'; DIVIDE([Sum of Amount]; [Number of Transactions]))Which gets you this:
2 Replies
PaulDBrownCommunity Champion
Anonymous
Try this:
the model:
And the measures:
Sum of Amount = SUM('DataTable'[Amount])Number of Transactions = DISTINCTCOUNT('DataTable'[Transaction ID])Average Purchase Value overall = DIVIDE([Sum of Amount]; [Number of Transactions])Average Purchase value per customer = AVERAGEX('User ID Table'; DIVIDE([Sum of Amount]; [Number of Transactions]))Which gets you this:
Hi
For a similar situtaion I had used this measure. Hopefully it works for you
Avregae by Group = CALCULATE(AVERAGEX(SalesRegionB,[AverageSales]),GROUPBY(SalesRegionB,SalesRegionB[SalesID]))Accept the solution if it works.
