Forum Discussion

acnt_schartner's avatar
acnt_schartner
Icon for Helper III rankHelper III
6 years ago
Solved

Logged Time to Average Time

Hello,

 

i have two Tables

Labels

Ticket_KeyTicket_Label
P-00001ACID-000001
P-00002ACID-000001

 

Worklogs

Worklog_IDTicket_KeyTime_Logged
WID_1P-000011
WID_2P-000011
WID_3P-000023.5

 

I want to compare the time spend for each ticket against the average of all tickets with the same label.

For example the average for the label "ACID-000001" is ((1+1)+3.5)/2= 2.75 and i need something like this:

Ticket-KeyAverage Time Spend (per label)Logged TimeLogged vs Average
P-000012.7520.727
P-000022.753.51.273

 

I am a bit stuck at this point and hope somebody can point me in the right direction.

 

Thanks in advance.

Need Help
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    6 years ago

    acnt_schartner 

    I got the results, please check and accept if it works for you.
    Add the following measures;

     

     

    Total = SUM(Worklogs[Time_Logged])

     

     

     

    Avg = 
VAR L = SELECTEDVALUE(Labels[Ticket_Label])
VAR T = 
    SUMX( 
        FILTER(ALL(Worklogs),RELATED(Labels[Ticket_Label])=L),
        Worklogs[Time_Logged]
    )
VAR DT = CALCULATE(DISTINCTCOUNT(Worklogs[Ticket_Key]),FILTER(ALL(Worklogs),RELATED(Labels[Ticket_Label])=L))

RETURN
DIVIDE(T,DT)

     

     

     

    Time Logged vs Avg = 
DIVIDE(
    [Total],
    [Avg]
)

     

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

7 Replies

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

    acnt_schartner , Create measures like


    Average Time Spend (per label) = calculate(divide(sum(Worklogs[Time_Logged]),distinctcount(Worklogs[Ticket_Key]), allselected(Worklogs))

    Logged Time = sum(Worklogs[Time_Logged])

    Logged vs Average = [Average Time Spend (per label)] -[Logged Time]

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    acnt_schartner 

    How do you calculate "Logged vs Average"? And, hope you have a relationship between the table on the Key?


    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

    • acnt_schartner's avatar
      acnt_schartner
      Icon for Helper III rankHelper III
      6 years ago

      Hi,

      Logged vs Average ist what i am looking for. It should be "Logged Time"/"Average Time Spend (per Label)"

       

      The relationship is based on "Ticket_Key".

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        6 years ago

        acnt_schartner 

        I got the results, please check and accept if it works for you.
        Add the following measures;

         

         

        Total = SUM(Worklogs[Time_Logged])

         

         

         

        Avg = 
VAR L = SELECTEDVALUE(Labels[Ticket_Label])
VAR T = 
    SUMX( 
        FILTER(ALL(Worklogs),RELATED(Labels[Ticket_Label])=L),
        Worklogs[Time_Logged]
    )
VAR DT = CALCULATE(DISTINCTCOUNT(Worklogs[Ticket_Key]),FILTER(ALL(Worklogs),RELATED(Labels[Ticket_Label])=L))

RETURN
DIVIDE(T,DT)

         

         

         

        Time Logged vs Avg = 
DIVIDE(
    [Total],
    [Avg]
)

         

        ________________________

        If my answer was helpful, please consider Accept it as the solution to help the other members find it

        Click on the Thumbs-Up icon if you like this reply 🙂

        YouTube  LinkedIn