cancel
Showing results for
Did you mean:
Helper I

## Logged Time to Average Time

Hello,

i have two Tables

Labels

 Ticket_Key Ticket_Label P-00001 ACID-000001 P-00002 ACID-000001

Worklogs

 Worklog_ID Ticket_Key Time_Logged WID_1 P-00001 1 WID_2 P-00001 1 WID_3 P-00002 3.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-Key Average Time Spend (per label) Logged Time Logged vs Average P-00001 2.75 2 0.727 P-00002 2.75 3.5 1.273

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Champion

@acnt_schartner

I got the results, please check and accept if it works for you.

``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 🙂

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

Proud to be a Super User!

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 🙂

Helper I

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

Community Champion

@acnt_schartner

I got the results, please check and accept if it works for you.

``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 🙂

Helper I

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.

Community Champion

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

Helper I

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### 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