Calculating average revenue per call

[jamesrupertball]

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 Identifier	Call Type
001	Type 1
002	Type 2
003	Type 1

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

Call Identifier	Call Type	Product	Revenue
001	Type 1	Product 1	$10
001	Type 1	Product 2	$25
002	Type 2	Product 1	$55
002	Type 2	Product 3	$60
003	Type 1	Product 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!

[v-xiaotang]

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

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]

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

[PaulOlding · 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])
)

[amitchandak]

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

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