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 need a measure where I get the sum of duration for a category which comes first.
For example:
I have a ticket system where the i have the ticket status: transmitted -> in process -> waiting for answer (when there is something unclear and the processor asks a question) -> solution
I need the sum of duration for either the ticket status "waiting for answer" or "solution", depending on wich category comes first.
I have also a column (Event_number), which sorts the ticketstatus.
Thanks!
Solved! Go to Solution.
hi @Julie_
supposing you have a table like:
case_id | TicketStatus | event_number | activity_duration |
1 | 0 | 1 | 101 |
1 | 10 | 2 | 102 |
1 | 15 | 3 | 103 |
1 | 20 | 4 | 104 |
1 | 30 | 5 | 105 |
1 | 35 | 6 | 106 |
1 | 45 | 7 | 107 |
2 | 0 | 1 | 108 |
2 | 10 | 2 | 109 |
2 | 15 | 3 | 110 |
2 | 35 | 4 | 111 |
2 | 30 | 5 | 112 |
2 | 20 | 6 | 113 |
2 | 45 | 7 | 114 |
try to plot a measure like:
Measure =
SUMX(
VALUES(data[case_id]),
CALCULATE(
MINX(
TOPN(
1,
FILTER(
data,
data[TicketStatus] IN {20, 35}
),
data[event_number],
ASC
),
data[activity_duration]
)
)
)
it worked like:
Either sum the duration for Ticket Status 20 or 35, depending on which Ticket status comes first.
In that case i would need the cumulative sum up to TicketStatus 20, but I have to also sum it with all case_ids.
the activity_duration is just the duration in milliseconds
hi @Julie_
supposing you have a table like:
case_id | TicketStatus | event_number | activity_duration |
1 | 0 | 1 | 101 |
1 | 10 | 2 | 102 |
1 | 15 | 3 | 103 |
1 | 20 | 4 | 104 |
1 | 30 | 5 | 105 |
1 | 35 | 6 | 106 |
1 | 45 | 7 | 107 |
2 | 0 | 1 | 108 |
2 | 10 | 2 | 109 |
2 | 15 | 3 | 110 |
2 | 35 | 4 | 111 |
2 | 30 | 5 | 112 |
2 | 20 | 6 | 113 |
2 | 45 | 7 | 114 |
try to plot a measure like:
Measure =
SUMX(
VALUES(data[case_id]),
CALCULATE(
MINX(
TOPN(
1,
FILTER(
data,
data[TicketStatus] IN {20, 35}
),
data[event_number],
ASC
),
data[activity_duration]
)
)
)
it worked like:
is there a possibility to just get the first of either ticket status 20 or 35?
and calculate then the cumulative sum till the first ticket status (20 or 35)
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |