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
jamesrupertball
Frequent Visitor

Calculating average revenue per call

Hello, I am trying to calculate average revenue per call type.  I have a call table and a revenue table.

 

Call table (shows number of calls by call type):

 

Call IdentifierCall Type
001Type 1
002Type 2
003Type 1

 

Revenue table (shows revenue by product and call type):

 

Call IdentifierCall TypeProductRevenue
001

Type 1

Product 1$10
001Type 1Product 2$25
002Type 2Product 1$55
002Type 2Product 3$60
003Type 1Product 1$15

 

What I am trying to caluculate is the total revenue per call type.  Essentially sum all of the revenue by call type (from the revenue table) and then divide by the number of calls of that type (from the call table).  E.g. Total revenue for Type 1 would be $50 and total calls for Type 1 would be 2 so the average revenue for Call Type 1 is $25.

 

Thanks for your help!

1 ACCEPTED SOLUTION

Hi @jamesrupertball 

Here's a measure to get average revenue per call

Average Revenue per Call = 
DIVIDE(
    SUM(Revenue[Revenue]),
    DISTINCTCOUNT(Revenue[Call Identifier])
)

PaulOlding_0-1652623328091.png

 

View solution in original post

4 REPLIES 4
v-xiaotang
Community Support
Community Support

Hi @jamesrupertball 

"Essentially sum all of the revenue by call type (from the revenue table) and then divide by the number of calls of that type (from the call table). ", If this rule is strictly followed, then measure can be written as

Measure = 
//from the revenue table
var _totalPerType=CALCULATE(SUM(Revenue[Revenue]),ALLEXCEPT(Revenue,Revenue[Call Type]))
//from the call table
var _numOfCall= CALCULATE(DISTINCTCOUNT('Call'[Call Identifier]),'Call'[Call Type]= MIN(Revenue[Call Type]))
return divide(_totalPerType,_numOfCall)

result

vxiaotang_1-1652773624364.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

jamesrupertball
Frequent Visitor

Thanks for your help.  That didn't quite work though.  It got to the right answer for Type 1 calls but not Type 2 calls. The calculation I am looking for is:

 

Total revenue/Total calls for each call type.

 

So for type 1 that would be ($10 + $25 + $15) / 2 = $25

So for type 2 that would be ($55 + $60) / 1 = $115

 

Thanks,

James

Hi @jamesrupertball 

Here's a measure to get average revenue per call

Average Revenue per Call = 
DIVIDE(
    SUM(Revenue[Revenue]),
    DISTINCTCOUNT(Revenue[Call Identifier])
)

PaulOlding_0-1652623328091.png

 

amitchandak
Super User
Super User

@jamesrupertball , Seem like sum till product Avg post that, Try like this with Type in visual

 

AverageX(values(Table[Product]), calculate(Sum(Table[Revenue]) ) )

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.

Top Solution Authors