Line/Stacked Column Chart, Orginization between seperate data sets and identifying matching cases.

[Umaxy]

 

Ok, so this is as far as I could get. Individually, these are the 3 visualizations I am hoping to combine. 

                            

One column is planned, committed, non-committed, with a line across indicating capacity(currently a dot).

3 datasets are being used.

What I have tried:

So I first started with determining what was still in Stage 1 from the Planning Phase, Added to Stage 1 that was not from the planning phase and lastly what was moved to stage 2. I used this

From Stage 1 Planning Phase = CALCULATE( COUNTROWS('Planned'), FILTER( 'Planned', 'Planned'[Title] = EARLIER('Committed/Actual'[Title]))) > 0

followed by in the next column 

Committed/Non-Committed = IF('Planned'[From Stage 1 Planning Phase] = True, "Committed", "Non-Committed")

This showed me simply what made it from planned and what pushed to stage 2. 

I then followed the same process with Committed/Actual data set to determine what was added to stage 1.

This is more or less how I created the graphs above using the sum of hours.

Now how do I combine these visualizations? 

So I started with attempting to combine all the data into one query then using an if statement to assign its status(Planned, Committed, Added, Not Committed). I had issues with merging the data as the data sets are not all the same. In the future, that will change but for now, it is where I'm at. 

Also when making one query I would only be able to assign committed, not committed and added. Plus I would miss out on the detail of where what came from. 

Any tips are greatly appreciated. I don't know how else I should attack this. 

Thank You

[Umaxy]

                            

One column is planned, committed, non-committed, with a line across indicating capacity(currently a dot).

3 datasets are being used.

What I have tried:

So I first started with determining what was still in Stage 1 from the Planning Phase, Added to Stage 1 that was not from the planning phase and lastly what was moved to stage 2. I used this

From Stage 1 Planning Phase = CALCULATE( COUNTROWS('Planned'), FILTER( 'Planned', 'Planned'[Title] = EARLIER('Committed/Actual'[Title]))) > 0

followed by in the next column 

Committed/Non-Committed = IF('Planned'[From Stage 1 Planning Phase] = True, "Committed", "Non-Committed")

This showed me simply what made it from planned and what pushed to stage 2. 

I then followed the same process with Committed/Actual data set to determine what was added to stage 1.

This is more or less how I created the graphs above using the sum of hours.

Now how do I combine these visualizations? 

So I started with attempting to combine all the data into one query then using an if statement to assign its status(Planned, Committed, Added, Not Committed). I had issues with merging the data as the data sets are not all the same. In the future, that will change but for now, it is where I'm at. 

Also when making one query I would only be able to assign committed, not committed and added. Plus I would miss out on the detail of where what came from. 

Any tips are greatly appreciated. I don't know how else I should attack this. 

Thank You

[Umaxy]

So i was able to sort this out. My issue was in how the data was managed.

I added a column defining each data set as committed, planned, non-committed. This I used for my shared axis.

I then appended the data into one list.

I then followed instructions form this source:

https://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/

This allowed me to make a case for each situation.

I then added a custom column with this if statement:

=if [Literal] = "Planned"
then "Planned"
else if [Literal] = "Committed" and [Match] = 1
then "Committed"
else if [Literal] = "Committed" and [Match] = 0
then "Spill"
else if [Literal] = "Non-Committed" and [Match] = 1
then "Non-Committed"
else "Do not include"

This was used as my Column Series.

Which produced the graph i was looking for.

Lastly, I have 2 more items i hope to tackle.

When entering in a line value it does not go across the entirety of the visual?

And how i could go about also splitting the planned column with "Non-Committed' and "Committed"?

[Anonymous]

HI @Umaxy,

I'd like to suggest you add column to group your status(Non-Committed and Committed), then use this column as legend field.

Regards,

Xiaoxin Sheng

[Umaxy]

So these are my options:

I am not able to add any other columns to "Column Series" or "Column Values".

Stage is how i sort between Planned, Committed and Noon-Committed.

Type is how I sort between committed, non-committed, spill, and planned.

Here is the visual again for referance.

[Umaxy]

So i performed the above example twice.

First to find the Items that were part of planned that made it into committed and non committed.

I then aggregated everything into one table to find the items for the other 2 columns.

Final Graph:

Lastly just need to figure out how to get the velocity line all the way across?

[Anonymous]

Hi @Umaxy,

You can use below measure to instead velocity column.

Measure =
CALCULATE ( SUM ( table[velocity] ), VALUE ( table[stage] ) )

Regards,

Xiaoxin Sheng

[Umaxy]

I’ll give it a shot

Thanks