cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Umaxy Frequent Visitor
Frequent Visitor

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

 

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

Capture.JPG                            

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

 

3 datasets are being used.

 

1.JPG

 

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

6 REPLIES 6
Umaxy Frequent Visitor
Frequent Visitor

Re: Planned/Commit/Non-Commit

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"?

 

Capture.JPG

Highlighted
Community Support Team
Community Support Team

Re: Planned/Commit/Non-Commit

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Umaxy Frequent Visitor
Frequent Visitor

Re: Planned/Commit/Non-Commit

So these are my options:

 

Capture.JPG

 

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.

 

1.JPG

Umaxy Frequent Visitor
Frequent Visitor

Re: Planned/Commit/Non-Commit

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:

Capture.JPG

 

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

Community Support Team
Community Support Team

Re: Planned/Commit/Non-Commit

Hi @Umaxy,

 

You can use below measure to instead velocity column.

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Umaxy Frequent Visitor
Frequent Visitor

Re: Planned/Commit/Non-Commit

I’ll give it a shot

Thanks

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,916)