Clustered bars for multiple series.

[Anonymous]

Hi, I cannot figure this out. I have 100's of unique ID's that are answering the question, how many days do you run, during snow and rain. columns below. I want to have a clustered bar chart that would have two columns (rain and snow) for each choice (0,1,2,3,4,5). Right now, the bars are not indicative of the results. I read that I need to make a measure for each category (0-5), but that is giving me 6 bars for each answer. I want to see the change in number of days running (rain and snow) together...right above 0-5. I assumed 6 dual bars...but it's not happening. Any suggestions?

[TomMartens]

Hey @Anonymous ,

I have to admit that by your descrIption I do not understand how your table(s) looks like.

Please provide a pbix with sample data, that still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you use Excel to create the sample data, share the xlsx as well.

Don't forget to describ the expected result based on your sample data.

Regards,

Tom

[Anonymous]

Link attached. Page 1 of power bi shows what I'm trying to do -combine 

https://www.dropbox.com/sh/vrg7r1bn20yl3t0/AACblCAi9X6jNqYDjtINeOhoa?dl=0 

@TomMartens  Thanks in advance.

[TomMartens · Accepted solution]

Hey @Anonymous ,

it's quite simple to achieve what you are looking for, without the creation of a measure, just by bringing the data into the proper format. Unfortunately, the explanation will become somewhat lengthy, for this, I start with the result:

This is how I used the columns (be aware that I did not name the columns properly), if you are wondering about the table Sheet1 - Long (I just copied and renamed the original table, to not break things):

Please excuse the weird names (Attribute and Value) but I still do not understand what your data is about.

It's important to understand that the data that forms a table has a shape, there are two types of shapes, wide and long. It's always a good idea to store the data that we want to analyze/visualize in a long format. Basically, a table in the long format has more rows, a table in a wide format has more columns. Differentiating what we already have and what we need is subtle, especially in your case. I transformed a wide table into a long format, basically, this means I reduce the number of columns. Here the final result also has 3 columns 🙂 Transforming a table into the long format, creates more rows. This is never a bad idea, as the database that fuels its power to Power BI is using columnar data compression, but this is not the right place to discuss database principles 🙂

I recommend reading this article to get a concise understanding of the two table formats, even if the technology that is used to transform the data is different: http://vita.had.co.nz/papers/tidy-data.pdf

To transform the existing data into a long structure,

• open Power Query
• mark the columns Run and Swim
• Select the Unpivot Columns in the Transform menu (the Any column ribbon)
• Name the columns properly 🙂

This article describes the command in more detail:

The long table structure will look like this:

Don't forget to rename the resulting columns accordingly 🙂

My reasoning behind this has been simple, I consider Run and Swim as different categories for some categorical variable like activity and the numbers 0, 1, ... as a numerical value. For this, it's necessary to transform the categorical variables into a single column.

This article describes both commands unpivot and pivot in more details:

https://radacad.com/pivot-and-unpivot-with-power-bi

Hopefully, this is what you are looking for.

Regards,

Tom

[Anonymous]

Professor, that. is. amazing. I can't thank you enough.