cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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.

1 ACCEPTED SOLUTION

Accepted Solutions

@acnt_schartner 

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

Fowmy_0-1599137714400.png

Fowmy_1-1599137728586.png

 

 

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

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

@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]



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Community Champion
Community Champion

@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

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".

@acnt_schartner 

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

Fowmy_0-1599137714400.png

Fowmy_1-1599137728586.png

 

 

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

View solution in original post

Hi,

thanks for your help, but can not use this messure, because i have a m:n relationship between Worklogs and Labels.

 

A Ticket-Key in Worklogs can have multiple entries (Worklog_ID).

And Ticket-Key in Labels can have multiple labels assigned to it (I am just interessted in the ACID but there are more in the actual backend). Filtering the Labels in Power Query M does not seem to work either.

 

 

@acnt_schartner 

In my example I set it as On-to-many based on your initial question and explanation. 


One option is to set your relationship to  On-to-many  or you can tell me where you have a Table wtih unique Ticket Key?
Creating a Unique Ticket Key dimention is another way.

________________________

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

Hi,

 

I managed to find a solution:

 

I just imported the Ticket_Labels into the Worklogs-Table. This only works because the Tickets i am interessted in only have one label. This solution wouldn't work if more labels for each ticket where needed.

I just created a new column (in DAX):  

Ticket_Label =LOOKUPVALUE(Labels[Ticket_Label],Labels[Ticket_Key],Worklogs[Ticket_Key],BLANK())

 

After that i could just adjust your messure to only use the Worklogs_Table.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors