Forum Discussion

Umaxy's avatar
Umaxy
Frequent Visitor
8 years ago

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.                               One column is planned, committed, non-commi...
Need Help
Umaxy's avatar
Umaxy
Frequent Visitor
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Umaxy
    Frequent Visitor
    8 years ago

    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's avatar
      Umaxy
      Frequent Visitor
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Umaxy,

         

        You can use below measure to instead velocity column.

         

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

         

        Regards,

        Xiaoxin Sheng