Logged Time to Average Time

[acnt_schartner]

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.

Thanks in advance.

[Fowmy]

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

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 · Accepted solution]

@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

[acnt_schartner]

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.

[Fowmy]

@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

[acnt_schartner]

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.

[amitchandak]

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