Target line, from data, for each Bar in Bar Chart to represent Individual Team Capacity

[James Ewen]

I'm trying to visualize the capacity of numerous Product Teams. We have a Portfolio of work and each row has a named Product Team accountable for it and an estimate value for the "size" of the work (this is a formula in Excel) and a Prioritization ranking by the customers. This is helpful in determining the backlog each team has, what items should get their focus, and how much each thing will take. In a separate excel file, we have each Product Team and their estimated, absolute capacity (again, calculated).

The story I want to show is that for each team there should be a bar chart and as product work is selected, the bar will increase. At some point, that bar will cross a threshold telling us that "that's it" the team can't do any more work. It would be great to show that one team can do their #1, 2, and some of #3 priorities, and other teams might only have enough resources to do #1 only.

I can achieve MOST of this using a Bar Chart that has Sum of Estimated Work on the Y-axis, "None" on the X-axis, and Trellis Columns using Product Team Name. Color by Priority value and I can see the bars grow & decrease and break down. However, I can't get each individual Trellis to show the respective Product Team's Capacity (as a target line). I can create individual lines if I select a calculated value (like Third Quartile), but I want this to come from Data. Each Product Team has a unique value for their capacity (they are of different sizes).

Is there a way to associate the line, based on Trellis filter, to the visualization? And if there is no corresponding data - the line just defaults to zero or none?
 

Here is an example of how the data is laid out:

Product Work	Product Team Responsible	Priority Ranking	Estimate of Effort
Update registration	Team A	1	100
Software Upgrade	Team B	2	100
Build UI Enhancements	Team A	1	1000
SaaS Renewal	Team C	3	10

And a separate table used to track the Product Team's capacity:

Product Team Responsible	Estimate of Capacity
Team A	840
Team B	230
Team C	85

 

I'm happy to modify the source tables, we have control, if combining them would produce the outcome I need.
Note: this is very similar to this old question, however this did not try to address creating lines from data: https://stackoverflow.com/questions/22602479/target-for-each-bar-in-spotfire-or-combination-of-line-and-stacked-barchart

 

[David Boot-Olazabal]

Hi James,

I have attached a dxp example, with two trellis options (Spotfire 14.0, so I hope you can open it). I have added the capacity as part of the initial data table, so you can easily set up a threshold line. The data model looks like this:

And the end result looks like this (from the first trellis option):

I hope this is what you were looking for.

Kind regards,

David

Trellis with Team capacity.dxp

[James Ewen]

Thank you David - this was exactly the direction I needed. By joining the "capacity" as a new column, it allowed the line to be drawn by the specific group in its own trellis. By having it come in as a separate table, it drew all of them (even overlapping). 

This creates the kind of view to help us understand a product team's capacity vs. the efforts prioritized & selected (I.e. in this draft all teams have more capacity to go ,except the two on the right that are way above capacity).

For other's looking to do this, for the final view I set the Category Axis to NONE, Value Axis to the Estimated Effort - Single Scale, Trellis by Product Team along Columns only, and the Line & Curve was Horizontal Line > Line from Data Table > and selected the recorded Team Capacity, set the Line to be One per: Trellis.