Stacked column chart with data from two tables

[sfink22]

I have two tables that I am trying to graph on one stacked column chart. One dynamic table contains dates for existing project phases, and one is a data table that contains % time to complete the phase based on complexity (that does not change).

For example, the project phase/date table would look like this:

Index	ProjName	Owner	Complexity Rating	Phase 1 Date	Phase 2 Date... (etc.)
1	Abc	Bob	4	2020P04	2020P05
2	Def	Jill	2	2020P01	2020P02
3	Ghi	Fred	1	2020P09	2020P10

And the % time for completion would look like this:

Complexity	Phase 1 Time	Phase 2 Time	Phase 3 Time	Phase 4 Time	Phase 5 Time
1	.05	.1	.15	.2	.1
2	.1	.15	.2	.25	.15
3	.3	.35	.4	.5	.3

I'm trying to create a "schedule" of sorts that shows the spread of work over time, that I could then add a slicer to in order to look at each project owner individually (they can own multiple projects per person). I need the dates to go on the x-axis and the sums of % time to go on the y-axis. The chart should look like this:

The "dates" are categorical values and not actual calendar dates, and they must remain this way as this is how it comes out of our system. The "0" added in for single digit periods (P01, P02 vs. P10) is necessary to get the categorical values to line up correctly on the graph's axis.

I've tried many ways to link the tables, but currently all I get is the same sums for each phase across all dates i.e. the same bars on every x-axis value. My trouble in linking comes because I don't have any way to link the dates. There could be any year/period combo across three years, but some dates may not show up if none of the phases align with that date. So in order to have dates on the x-axis, I have to add one of the phases, but it doesn't necessarily align with all dates from the other projects or phases. In other words, the x-axis can't be linked between the two tables. 

[v-lionel-msft · Accepted solution]

Hi @sfink22 ,

You can do like this.

1. Get such a table by transforming(copy + append queries)

2. Create table relationship.

3. Create calculated columns in [Date] table.

P1_time = 
LOOKUPVALUE(
    '%time'[Phase 1 Time],
    '%time'[Complexity], 'Date'[Complexity Rating]
)

P2_time = 
LOOKUPVALUE(
    '%time'[Phase 2 Time],
    '%time'[Complexity], 'Date'[Complexity Rating]
) 

4. Add the calculated columns and the [Date] column to the visual.

Best regards,
Lionel Chen

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

[Greg_Deckler]

So are the tables supposed to be related on Complexity?

I would unpivot the % columns in your second table. Create a bridge table of distinct Complexity values. Create relationships on complexity columns, use the complexity column from your bridge table.

[sfink22]

Yes, they are related by complexity. By a bridge table, do you mean a table that has a single column with the unique complexity values?