Forum Discussion
Need help calculating sum...
Hello! I am very new to PowerBI. I apologize if I can't properly search nor phrase my question correctly.
So currently, I have a table that looks like this:
| Case Number | Reject Reason 1 | Reject Reason 2 | Reject Reason 3 | Reject Reason 4 | Reject Reason 5 | Reject Reason 6 | Reject Reason 7 | Reject Reason 8 | Reject Reason 9 |
| Case1 | 1 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | 1 |
| Case2 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 1 |
| Case3 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
So we track the data by which reject reason is applicable per case. And it's only presented by 0 and 1.
Now, I wanted to make another table that summarizes the number of reject reasons like this:
| Reject Reason | Total Number |
| Reject Reason 1 | x |
| Reject Reason 2 | x |
| Reject Reason 3 | x |
| Reject Reason 4 | x |
| Reject Reason 5 | x |
| Reject Reason 6 | x |
| Reject Reason 7 | x |
| Reject Reason 8 | x |
I was wondering if there is any function or formula that would automatically lookup the text from the 1st column (the reject reason) to the 1st table and then summarize or calculate the number so that it would look like this:
| Reject Reason | Total Number |
| Reject Reason 1 | 3 |
| Reject Reason 2 | 2 |
| Reject Reason 3 | 2 |
| Reject Reason 4 | 2 |
| Reject Reason 5 | 1 |
| Reject Reason 6 | 2 |
| Reject Reason 7 | 0 |
| Reject Reason 8 | 2 |
| Reject Reason 9 | 2 |
Thank you in advance!
The process you are looking to do is "Unpivot". This is easiest done using Power Query.
Click on Transform Data, then select Transform.
Select your Reason Code Columns, then select Unpivot - Selected Columns.
Then you can create all of your Visuals using this table.
Hope you can get this to work for you.
Regards,
2 Replies
The process you are looking to do is "Unpivot". This is easiest done using Power Query.
Click on Transform Data, then select Transform.
Select your Reason Code Columns, then select Unpivot - Selected Columns.
Then you can create all of your Visuals using this table.
Hope you can get this to work for you.
Regards,
Thank you! This is exactly what I was looking for!
