Average calculation on data between two tables

[30rakesh]

Hi,

I have two tables. One is 'Incident' and the second is 'HoldActivity' (and relation between these two tables is 'IncId' as shown below).  I need to display a visual which should show the average of TotalDuration and HoldDuration. When I apply the average, it shows the TotalDuration as 125 (i.e. 100+150/2) and HoldDuration as 50. 

Actually, I need HoldDuration to display as 25. Can you anyone help me in solving this issue.

Incident

HoldActivity  

 

Thanks

Rakesh

[SabineOussi · Accepted solution]

You need to add something like this

DIVIDE(
HoldActivity[HoldDuration],
DISTINCTCOUNT(Incident[IncId])
)

if you are calculating the average of HoldDuration based on the number of incidents in table Incident.

[30rakesh]

Thank you SabineOussi!!

It worked.

[SamLester]

What logic are you using to determine that the value should be 25?

Thanks,
Sam Lester (MSFT)