Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 IDUserIDAmountDate
1212121121/12/19
131414151122/12/19
1342425232121/12/19
54353543121/12/19
2342432252/12/19
3243252132253/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!! 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@Anonymous 

Try this:

the model:

model.JPG

 

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:

Result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
ybcrg
Regular Visitor

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.
 
PaulDBrown
Community Champion
Community Champion

@Anonymous 

Try this:

the model:

model.JPG

 

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:

Result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.