Forum Discussion
Sum Like Values from Differing Columns
Hello,
I am trying to sum like values from different columns across multiple records. I can't seem to figure out to how to get them to sum all together.
Here is an example data set:
Record ID Category #1 Category #2 Category #3 Category #4 Record Count
| 1 | A | B | 1 | ||
| 2 | B | 1 | |||
| 3 | B | D | F | 1 | |
| 4 | B | B | 1 | ||
| 5 | C | 1 | |||
| 6 | C | E | 1 | ||
| 7 | C | E | E | 1 | |
| 8 | C | B | C | C | 1 |
| 9 | C | C | C | B | 1 |
| 10 | C | F | 1 |
What I am trying to do in PowerBI is return a value that displays the sum total like so:
A = 1
B = 7
C = 10
I would like this displayed in a graph if possible as well. Any help would be appreciated.
One option would be to unpivot your Category columns using the Power Query Editor
- Click Edit Queries
- Select all Category columns
- Go to the Transform tab and click "Unpivot Columns"
This will take your table that currently looks like this:
And turn it into this:
You can now create a visual and simply drop the "Value" column (or whatever you end up naming it) into both the Axis (or legend) and Value fields (Count of Value). Finally, just filter the visual to not include records where the value is blank.
1 Reply
One option would be to unpivot your Category columns using the Power Query Editor
- Click Edit Queries
- Select all Category columns
- Go to the Transform tab and click "Unpivot Columns"
This will take your table that currently looks like this:
And turn it into this:
You can now create a visual and simply drop the "Value" column (or whatever you end up naming it) into both the Axis (or legend) and Value fields (Count of Value). Finally, just filter the visual to not include records where the value is blank.