cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Super User

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 2
Super User

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,

Frequent Visitor

Thank you! This is exactly what I was looking for!