Clustered column chart: how to organize data?

[Anonymous]

Hello,

I want to create clustered column charts that illustrate answers to the same survey questions over the years.

The data are imported from Excel: each sheet has the same set up, same questions. One sheet is for 2018, one for 2019 , one for 2020.

Once in Power BI, the relationship is created between the 3 sheets.

The graph needs to show the responses to question X over the 3 years, side by side:  three bars showing that in 2018, the cohort average response was 10%, in 2019, it was 20% and in 2020 it was 15%.

Ideally, the slicer would be the selection process to go throug the survey questions.

Excel organization: column header is the question itself, each beneath has the participant response (numeric, 1 to 4) so as to calculate the cohort average.

Thank you.

[Anonymous]

Thank you for suggestions.

I already am familiar with that feature and have used it elswhere.

The main question is: would this allow me to create a collumn chart where I can see data for the 3 cohorts side by side?

Thank you.

[v-deddai1-msft]

Hi,

You can drag year column to legend:

Best Regards,

Dedmon Dai

[v-deddai1-msft · Accepted solution]

Hi @Anonymous,

You can combine all the sheets by query editor. Please refer to the detailed steps in  https://radacad.com/combine-multiple-or-all-sheets-from-an-excel-file-into-a-power-bi-solution-using-power-query-dynamically

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

Best Regards,

Dedmon Dai

[mahoneypat]

You should not load your data as three separate tables.  Please see this link to learn how you can "Combine & Edit" the 3 files.  This will make your analysis much easier.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat