Stacked bar chart to depict a person's plan

[Anonymous]

Hi, 

I have the following data:

Index	Person	Activity	Start time	Duration (hours)
1	Bob	Home	0:00	7.0
2	Bob	Travel	7:00	1.0
3	Bob	Work	8:00	10.0
4	Bob	Travel	18:00	1.0
5	Bob	Home	19:00	5.0
6	Charlie	Home	0:00	9.0
7	Charlie	Shop	9:00	2.0
8	Charlie	Work	11:00	12.0
9	Charlie	Home	23:00	1.0

I'd like to create a stacked bar chart as follows:

The axis should be vertical, and based off 'Person', with the bars horizontal

Each stack should be based off 'Index' (i.e. one stack per row in the table)

Each stack should be of size 'Duration'

The stacks should be sorted by 'Start time'

The stacked should be coloured by 'Activity'

This will allow me to see each person's plan for the day so I can compare the two people easily and see what activities they have in common. 

Is this possible?

Thanks for the help! 

[amitchandak]

@Anonymous ,

Put the person on Axis and Activity on Legend and duration on the values.

If you sum up duration, use

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[Anonymous]

Thanks @amitchandak but this doesn't quite answer my question. 

Note that Bob travels twice, and is at home twice. What your suggestion will do is sum those together, and have only 3 stacks for Bob (i.e. one stack for home which sums the two home durations, one for travel which sums the two travel durations, and one for work). Instead I want 5 stacks for bob (i.e. one per row in the table) with the two 'home' and 'travel' activities split out separately. 

Any other ideas? 

[Syndicate_Admin]

Hi @elladahan ,

Perhaps so?

Use the stacked bar chart and add fields as shown in the image, you will have one hierarchy level, click to expand an entire level in the hierarchy

Best looks,
Yingjie Li

If this post helps, please consider Accepting it as the solution to help other members find it more quickly.

[v-yingjl]

Hi @Anonymous ,

Maybe like this?

Use the stacked bar chart and add fields like the picture shows, it will have a hierarchy level, click to expand all down one level in the hierarchy

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-yingjl  that kind of works, but it's not the best visualisation as it doesn't allow a nice comparison between people. For example, I can't easily see the difference in people's working start/end times. 

I was hoping for something like this:

I know this is possible to do in Tableau (sorry!) by having the stacks by ID (i.e. in 'Legend' you would use 'Index'), and then the colouring by Activity (currently the colouring is automatically based on the Legend categories in Power BI).

Any thoughts? 

[v-yingjl · Accepted solution]

Hi @Anonymous ,

If we use stacked bar chart and put the activity field as legend, it will sum the hours automatically by default. Also, legend column can only put one field so if we want to get it, we can just set the time or index as the legend and adjust the colors mannually in Data colors menu.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-yingjl  that works for a small example dataset. However, in my dataset I have one million people, with up to 50 activities each, all starting at different times of day. The graph is of course filtered to subsets so I only view about 30 people at one time.

The data is updated periodically, therefore times and activities change all the time. Manually adjusting the colours is not going to be a viable option for this.