Creating a Stacked Bar Chart with data from Multiple Columns AND a column with Multiple Values

[the_kent]

Hi all,

Extensive Excel user (sans VBA) but relatively new to PowerBI here.

I'm trying to transform some oldish table summary reports to Live dashboards in PowerBI hence in a quandry in translating excel tricks in summarizing oddly structured data to PowerBI's mode of reading data. 

Here's the Q:

I'm trying to create a stacked bar chart which references multiple columns AND this data is to be cross referenced with Categories against that Row, but this cell with said Categories has multiple values. 

Sample table structure below:

Row	Company 1	Company 2	Company 3	Category	Score
Item 1	Yes			Cat A, Cat B	10
Item 2	Yes	Yes		Cat A	20
Item 3			Yes	Cat C	30
Item 4		Yes		Cat A, Cat C	50
Item 5	Yes		Yes	Cat A, Cat C	10
Item 6	Yes			Cat A, Cat C	20

So, the stacked chart needs to sum the 'Score' against a certain Company AND categorising it against each of the Categories mentioned in the 'Category' column. The Stacked Chart pillar is per the Category.

The actual data has about 8 companies and 5 Categories 

Expected outcome:

Any help on this would be great.

If there's a better way of even organising the base data to help with this more intuitively - I definitely have the opportunity to transform the data from the 'old' to the 'new'.

More info on data - ~700-1000 lines a month *12

My current thinking is to create a secondary table which uses Excel like SumIFs to make a matrix of the data, and then create a stacked chart on it.

But if I were to do this, would filtering/ slicers on the base data also work on this new table (i.e. would they be linked)? (and thus the graph output)

[SanjayJohn]

anyone help me to create stacked bar chart

based on below data

many thanks for your anticipated help

[the_kent]

You're a star to take time out of your day for a detailed response, thank you so much!

1) Yes, I figured out the duplication thing immediately after posting here, that worked as needed! And just re-did the reports.

2) Got it, and I see it in my data as well

While I'll take time to digest the code, just want to quickly run you through my Unpivot logic if you've time (else no worries, I'll do some Trial and Error)

An issue I faced was the score sums against the categories post Unpviot as against the original data was not matching. I'm guessing this is because I did not include the 'score' as part of both phases of unpivoting.

My understanding of Unpivot was that columns not selected would just be replicated against the Columns being unpivoted - but I see in your code that it is part of the unpivot as well.

Here's what I did

1. Unpivoted the companies (without selecting the score) - 
2. Used text to columns with a delimiter to separate the categories
3. Unpivoted the categories (without selecting the score)

Having done this, I got the reports I wanted in a much better fashion (even other reports where previously to summarise company wise scores I had put a quick measure on the Score with filter on Company Column = Yes)

BUT the score totals went for a toss 

Again - on this mismatch I'll run some trial and error soon in my own time - but if you have the time, do help refine my understanding of the unpivot logic 🙂 

Thanks so much!

[BA_Pete · Accepted solution]

@the_kent ,

No worries. I'm guessing your score values went through the roof when you tried to sum them as you've not reaggregated the data into unique row sets.

This is why in my steps I had the Group By step at the end. This reaggregates on an average basis (not sum!) the duplicated rows that were created by the unpivot steps. Average works here as we know we are only grouping duplicated rows, so the average of them will just be the original value that was duplicated.

Unpivoting once isn't a problem as the data is recategorised correctly into a normalised structure. It's when we unpivot twice (due to two lots of different dimensions needing to be normalised) that we then get erroneous duplications. We are essentially creating a crossjoin on the second unpivot against each of the previously normalised values from the first unpivot.

TL;DR - You have to do the Group By step to reaggregate your scores correctly.

*EDIT* I ws a bit confused reading where you say that I'd included [score] in my unpivot columns so I rechecked it and it appears this way because Power Query has actually done a code switch. I used the Unpivot function from the GUI which you would expect to produce the function Table.UnpivotColumns(theColumnsYouHadSelected). However, it's actually turned it on its head an created the M code as Table.UnpivotOtherColumns(theColumnsYouDidn'tHaveSelected). A bit confusing, to be sure, but correct in terms of output.

Pete

[the_kent]

Gotcha! Will try it out!

Edit: It was the other way around in terms of data mismatch - I got lesser total values. 

Odd because the Sum by Category was on-point.

I also realised that some values in 'Category' were blank - this could be the issue I think - anyways shall be away tinkering and try that code as well!

[BA_Pete]

Hi @the_kent ,

First, you need to normalise your data structure in Power Query.

Unpivot the three company columns so you end up with a company column that denotes which company the data row is for.

Then split your category column by delimiter ',' to split all of your listed categories into their own columns.

Then unpivot your category columns so you have a single category column that denotes the category for each data row.

Then group your table on Company and Category and add an aggregated field of Average of Score. You should now have a table whereby you can use the new category field as your axis, the new company field as your detail, and a SUM(table[Value]) measure as your values in a 100% stacked chart or similar.

Pete

[the_kent]

Hey there, thanks for responding!

Again, being a newbie in this, appreciate the patience on the following queries.

Explored Unpivot in PowerBI through Transform data.

1. Is there a way to output the transformed data into a separate table? I.e. will transforming data in Unpivot re-write the base data as well?

2. Will whatever transformation I do, (i.e. Delimit, get all data in columns with individual values, and then unpivot), be repeated automatically as and when new data is entered in the original format? 

Thanks again for your help!

[BA_Pete]

@the_kent ,

1) It depends what you mean by "re-write the base data". It will not change anything in the data source (Excel file, SQL Database etc.), but it will change the original table as it was when it loaded in Power Query. To avoid this, you can select your original table on the left of Power Query and copy/paste, or right-click and select Duplicate or Reference, then perform the transformations on this new table.

2) Yes. every step you perform in Power Query is applied between the original data being imported from the source and the resulting query being passed to the data model. The steps I've given you are all generic and reference no specific data values that can change, so no issue there. The only thing that will break it is if you make changes to any of the columns that are used in the transformations at source e.g. renaming the column at source or deleting the column at source.

Now I'm back at my desk, I've performed the steps I gave to you before and refined them a bit.

Here's the M code that you can use to see the correct steps to take.

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRikwtBpIKUOycWKLgqKMAopyAXEMDpVgdqGIjuGKEFrByIG2EpM4YyTiISpAqZyBtjKTKBEUFmtUgxaZIik1RFCIMRVKO7FIzfN5yhjo3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, #"Company 1" = _t, #"Company 2" = _t, #"Company 3" = _t, Category = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", type text}, {"Company 1", type text}, {"Company 2", type text}, {"Company 3", type text}, {"Category", type text}, {"Score", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Row", "Category", "Score"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Category", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Category.1", "Category.2"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Row", "Score", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns1", each ([Value] = "Yes")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Attribute", "Value.1", "Row"}, {{"score", each List.Average([Score]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Attribute", Order.Ascending}, {"Value.1", Order.Ascending}})
in
    #"Sorted Rows"

I've left all the default columns names in so it looks exactly the same when you perform the steps, but it's easy enough to change the column names to more intuitive values later directly in the code.

This gives me the following output based on your example data:

Pete