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

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

Highlighted
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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 134 members 1,551 guests
Please welcome our newest community members: