Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

calculate time spent in each state

i have a data as given below

Ticket numCreatedstate
Ticket_111/3/2022 0:06P4
Ticket_111/3/2022 0:18P4
Ticket_111/3/2022 0:21P3
Ticket_111/3/2022 0:22P2
Ticket_111/3/2022 0:23P1
Ticket_111/3/2022 0:24P1
Ticket_111/3/2022 0:26P1
Ticket_212/12/2022 13:40P1
Ticket_212/12/2022 13:41P1
Ticket_212/12/2022 13:43P2
Ticket_212/12/2022 13:45P2
Ticket_212/12/2022 13:45P4
Ticket_212/12/2022 13:46P3
Ticket_212/13/2022 14:00P1


i would now like to calculate time spent by each state in hours/seconds for every ticket  in a 100% stacked bar chart

and see the transition for  state for Ticket_1 which changes from P4-->P3-->P2-->P1

Final chart to look something like this




Resolver I
Resolver I


my statement would be that a stacked bar visual cannot work and I would like to here from the communtiy if this statement is wrong or not. My reasoning is as follows:

I have done a little toy model similar to yours resuling in the following table:




The main observation is that I need an index or the timestamp to define the sequence of durations in percent. So, I have a value (the percentage) and three other dimensions (state, sequence and ticket id). 



The matrix visual can capture all dimensions as you can always put drilldown filelds in rows or colums. However the bar chart is one dimension short. You must put in the sequence to maintain the order and then you would have to put the state in the quick info. Which is clearly not what you want. As soonn as you put in the state for the category you loos the sequence and in this case all s1 values would be summed up per ticket, although in different phases. 


Perhaps somone know it better than me. At the moment my statement would that no standard visual can do what you want. 

I am curious to know if this is not the case. 


Best regards 






Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors