Time logged per Ticket regardless of user but total for each user

[acnt_schartner]

Hello, 
I am looking for a measure that can solve my  Problem. I have a table

WorklogID	TicketKey	Category	Time_Logged	User
01	101	ACID-01	1	A
02	101	ACID-01	1.5	B
03	102	ACID-01	2	A
04	103	ACID-02	2.5	C

I want the total sum of logged hours for each ticket assigned to each user, who logged time on a ticket. So user A and B would need to get the value 2.5 (TicketKey 101 has two users who logged time and i need the total for both). If i exclude the users with ALLEXCEPT i get the right value for the ticket but the total sum for the user is wrong (it uses all Tickets).

Thanks in advance

[Fowmy]

@acnt_schartner 

What do you expect for each user?

The following will give you by ticket when you add it as  new column

Column = 

CALCULATE(
    SUM(Table6[Time_Logged]),
    ALLEXCEPT(Table6,Table6[TicketKey]))

________________________

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]

Here is what i need for each user. 

The goal is to use the total time logged for a ticket (i.e. P500194-84) 0,66+0,73=1,39 for each user who logged time on that ticket (here A and B).

The result should also be correct if i sort a matrix visual by users. User A shold have the total 1,39+1,75=3,14

[Fowmy]

@acnt_schartner 

Did you try my formula? let me know if you face any issue.

________________________

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]

Did not work. Only thing that is ok is the sum at user A 1,39. The rest if completely off.

At the user (A B) and category(ACID) level it should sum up the logged times for each user. I want the tickets, where multiple people worked together, to be the sum of the total time spend on this ticket for each user.

EDIT: Each Ticket can have multiple entry from the same user. If i use your column i get the correct value for each worklog entry but not for the ticket per user

[Greg_Deckler]

@acnt_schartner - Reading this thread it seems that you want to change how things are aggregated at different levels of the hiearchy then you need MM3TR&R - 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

ISINSCOPE and HASONEVALUE are your friends in your situation.

[acnt_schartner · Accepted solution]

I was able to make it work (to some extend):

New Column 1

Logged_per_Ticket = CALCULATE( SUM(Worklogs[Time Entry Logged Time]),ALLEXCEPT(Worklogs,Worklogs[TicketKey]))

This adds the total time spend per TicketKey to each worklog entry. 

New Column 2

Worklogs_ID_Count = CALCULATE(DISTINCTCOUNT(Worklogs[Worklog ID]))

Now i can create a measure that displays the correct Value for each Ticket:

Measure = SUM(Worklogs[Logged_per_Ticket])/SUM(Worklogs[Worklogs_ID_Count])

I have still a problem with my totals

I do not want the totals on the categories to be used to calculate the value for Measure.
NOW: (102+2,57)/9= 11,62

GOAL: ((102/8)+(2,57/1))2 = 7,66

Is there a way to filter the measure?

EDIT: I got it:

Measure 1 = SELECTEDVALUE(Worklogs[Logged_per_Ticket])/SELECTEDVALUE(Worklogs[Worklogs_ID_Count])
only calculates on the ticket level (no totals).
Measure 2 = AVERAGEX( KEEPFILTERS(VALUES(Worklogs[Schlüssel])),CALCULATE([Measure 1]))

this adds the correct average i need in the totals for category (ACID) and user.

[amitchandak]

@acnt_schartner , Not very clear

Try like

new column = sumx(filter(table, [TicketKey] =earlier([TicketKey])),[Time_Logged])

new measure = calculate(sum([Time_Logged]),filter(allselected(table), [TicketKey] =max([TicketKey])))