Forum Discussion
Clustered column chart: how to organize data?
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.
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
4 Replies
- mahoneypat
Microsoft Employee
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
v-deddai1-msftCommunity Support
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
- v-deddai1-msft
Hi,
You can drag year column to legend:
Best Regards,
Dedmon Dai
