Stacked Column Chart with Multiple Columns of Data

[Noah]

Hello everyone.

I am struggling to find out of Power BI is capable of doing a specific clustered column with the following columns (you can find them in the shared sample excel spreadsheet):

1. List of SKUs
2. Stores
3. Lead times for process 1
4. Lead times for process 2
5. Lead times for process 3
6. Lead times for process 4
7. Total process time (sums per row)

The idea is to have a stacked column chart with multiple columns. Each column would represent the average lead times for each process, with side-by-side columns for each Store. For example, the first 2 columns would represent the average Lead times for Process 1 for Store A and Store B.

My actual dataset is structured exactly like in the sample spreadsheet and I can modify it to be structured differently if necessary.

Thanks for any help anybody can provide!

[Irwan · Accepted solution]

hello @Noah 

is this what you are looking for?

1. you need to unpivot your sample data abov using power query.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc67DcAgDATQXVxTYEP4lMkKKRH7rxFfbEtJ4RPwDsRadDMlOnW6DleNghHaSVEcGS0eGjiRYVpcjx9Ow+ooWDAq+aPYX4CJ6H63sGkLlai8n8qm3bXFw+y4Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, Store = _t, #"Process 1" = _t, #"Process 2" = _t, #"Process 3" = _t, #"Process 4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"Store", type text}, {"Process 1", Int64.Type}, {"Process 2", Int64.Type}, {"Process 3", Int64.Type}, {"Process 4", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SKU", "Store"}, "Attribute", "Value")
in
#"Unpivoted Columns"

2. Visualize your data (not sure if you want to do in stacked column since it will show 100% because there is only one value in each column visual)

If this not what you are looking for, please provide your sample data and/or your excel chart as PBI experts mentioned here.

Hope this will help.

Thank you.

[Anonymous]

Hi, @Noah 

I create a sample table:

Then unpivot the column in Power Query:

Create a stacked column chart and put fields in it:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Irwan · Accepted solution]

hello @Noah 

is this what you are looking for?

1. you need to unpivot your sample data abov using power query.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc67DcAgDATQXVxTYEP4lMkKKRH7rxFfbEtJ4RPwDsRadDMlOnW6DleNghHaSVEcGS0eGjiRYVpcjx9Ow+ooWDAq+aPYX4CJ6H63sGkLlai8n8qm3bXFw+y4Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, Store = _t, #"Process 1" = _t, #"Process 2" = _t, #"Process 3" = _t, #"Process 4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"Store", type text}, {"Process 1", Int64.Type}, {"Process 2", Int64.Type}, {"Process 3", Int64.Type}, {"Process 4", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SKU", "Store"}, "Attribute", "Value")
in
#"Unpivoted Columns"

2. Visualize your data (not sure if you want to do in stacked column since it will show 100% because there is only one value in each column visual)

If this not what you are looking for, please provide your sample data and/or your excel chart as PBI experts mentioned here.

Hope this will help.

Thank you.

[Noah · Accepted solution]

After I originally responded, I made the mental connection and figured out that Power BI can unpivot my dataset. That is exactly what I needed.

Thank you very much!

[Noah]

Yes! That's exactly how I need the graph to look!

Would I just replace each #"Process X" with my actual column names? 

[lbendlin]

Consider using a ribbon chart without the ribbons. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ryan_mayu]

what's the expected output what you want? could you pls also provide that in excel? And it's better that you can also create the chart in excel as well.