Forum Discussion

30rakesh's avatar
30rakesh
Regular Visitor
10 years ago
Solved

Average calculation on data between two tables

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

Need Help
  • SabineOussi's avatar
    SabineOussi
    10 years ago

    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.

     

     

     

3 Replies

  • SabineOussi's avatar
    SabineOussi
    Skilled Sharer
    10 years ago

    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.

     

     

     

  • SamLester's avatar
    SamLester
    Microsoft Employee
    10 years ago

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

     

    Thanks,
    Sam Lester (MSFT)