Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Here's a measure to get average revenue per call
Average Revenue per Call =
DIVIDE(
SUM(Revenue[Revenue]),
DISTINCTCOUNT(Revenue[Call Identifier])
)
"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.
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
Here's a measure to get average revenue per call
Average Revenue per Call =
DIVIDE(
SUM(Revenue[Revenue]),
DISTINCTCOUNT(Revenue[Call Identifier])
)
@jamesrupertball , Seem like sum till product Avg post that, Try like this with Type in visual
AverageX(values(Table[Product]), calculate(Sum(Table[Revenue]) ) )
User | Count |
---|---|
41 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |