1

I'm trying to create a clustered column chart that would show a sum of values, by month for each person with each cluster representing the same month. The data that are in the following format:

enter image description here

I need to create something like this:

enter image description here

Each bar is a person with a sum for the specific month. The person is appearing multiple times in the table for each month so we need a sum for each month.

I've managed in power bi to create a chart grouped by person and month, but i need the opposite as explained above:

enter image description here

Any ideas?

Themistoklis Bogiatzoglou
  • 459
  • 2
  • 7
  • 21
  • You need to unpivot your data first. So there will be one row for each date for a person. Then you can achieve your required output using clustered bar chart. – mkRabbani Jul 28 '21 at 11:18

1 Answers1

1

It looks like you have a lot of columns. The correct way to handle this would be to unpivot your table to transform your columns into rows. See this small sample table:

Table, prior to unpivoting.

We want to transform the data so that for each column you get one row with Personen, Date and Value. To do this, you can select the name column (and other columns that you want to not pivot), right click, and select Unpivot Other Columns. This will yield the following:

Table, after unpivoting.

You can edit the column names and data types to get the following table:

enter image description here

And here is your visual, using Date as Axis information, Personen as Legend information:

Finished!

Marcus
  • 3,346
  • 1
  • 5
  • 23
  • 1
    Thanks, working perfectly. Approved and upvoted your answer. One last question. What if we want to provide new data to report? should we unpivot again? is there a way to dynamic do all this thing when we provide new data? – Themistoklis Bogiatzoglou Jul 28 '21 at 13:07
  • So the `Unpivot Other Columns` command should handle new columns just fine, since you select only the columns you want to NOT unpivot. However, this data structure is not ideal since there is a fairly low upper limit of columns Power Query can handle (16000 I think!). Are you manually structuring the data in this way? If so, I would change my routines (!) , if not, perhaps the system that outputs the data can output the data in an unpivoted fashion. – Marcus Jul 28 '21 at 13:19