Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to Solution.
@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:
Proud to be a Super User!
Paul on Linkedin.
Hi
For a similar situtaion I had used this measure. Hopefully it works for you
@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:
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |