Let's see about these challenging Pivot Table and Charts - Issue Tracking

[Frank Bacchus]

Hi:

My attached file contains data to simulate the tracking of issues. I would like to create a few pivot tables with pivot charts that show the following:

(1) Average number of days that assignee has issue since opened by effort level. So this is by assignee, by effort level, the average number of days for open issues

(2) Average number of days, across effort levels that issues are open

(3) Average number of days, across the effort level that issues take to close (note for ‘closed’ issues, the modified date is the close date)

(4) Average number of days, across the effort level, by assignee that issue take to close (note for ‘closed’ issues, the modified date is the close date)

(5) And for the challenging of, using the slicer, would like to select an assignee, and their issues populates a table below the chart.

Please let me know if you gave any questions.

Thanks

frank

 

[Chihiro]

Clarification needed.

1. # of days open - Is it Today - Create Date? Or Due Date - Create Date? I ask since you have future dates for create date.

 

[Frank Bacchus]

Clarification needed.

1. # of days open - Is it Today - Create Date? Or Due Date - Create Date? I ask since you have future dates for create date.

Sorry. yes you are right, Number of days still open is Today - Create Date; Number of days Opened (issue is now closed) modified date - Create date.

thanks
frank

 

[Chihiro]

In that case, you can add helper column for Duration calculation.

Depending on size of your data, you may want to load the table to PowerQuery and add calculated column there. Then load the result to Data Model.

I'll upload sample once I get home tonight.

 

[Chihiro]

See attached, using helper column. Slicer to control both chart and pivot.

You can have bit more control using PowerQuery if you have access to it.

 

[Frank Bacchus]

In that case, you can add helper column for Duration calculation.

Depending on size of your data, you may want to load the table to PowerQuery and add calculated column there. Then load the result to Data Model.

I'll upload sample once I get home tonight.

Thanks Chihiro:

I like the charts and am still review them. The overall average per individual is great but I believe that the true story is his/her average within the effort level because each effort level has an 'effort time' associated with it. So an 'H' issue is harder to close and carries a different weight than a 'M' level issue. So is there a way to create a pivot table that shows the average days to close by individual by their effort level. This way we can compare Mary's effort level in 'M' issues with Thomas's effort level in 'M' issues. I'm uploading a new file. Thanks, way ahead.

frank

 

[Chihiro]

Hmm? That info is already included. Just expand the collapsed field and you should see avg by Effort Level for each assignee.