Forum Discussion

jamesrupertball's avatar
jamesrupertball
Frequent Visitor
4 years ago
Solved

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!

  • PaulOlding's avatar
    PaulOlding
    4 years ago

    Hi jamesrupertball 

    Here's a measure to get average revenue per call

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

     

4 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

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

     

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

  • jamesrupertball's avatar
    jamesrupertball
    Frequent Visitor
    4 years ago

    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's avatar
      PaulOlding
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi jamesrupertball 

      Here's a measure to get average revenue per call

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