Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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;
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |