Skip to main content
cancel
Showing results for 
Search instead 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

Reply
faustusxanthis
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 NumberReject Reason 1Reject Reason 2Reject Reason 3Reject Reason 4Reject Reason 5Reject Reason 6Reject Reason 7Reject Reason 8Reject Reason 9
Case1101101011
Case2110011011
Case3111100000

 

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 ReasonTotal Number
Reject Reason 1x
Reject Reason 2x
Reject Reason 3x
Reject Reason 4x
Reject Reason 5x
Reject Reason 6x
Reject Reason 7x
Reject Reason 8x

 

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 ReasonTotal Number
Reject Reason 13
Reject Reason 22
Reject Reason 32
Reject Reason 42
Reject Reason 51
Reject Reason 62
Reject Reason 70
Reject Reason 82
Reject Reason 92

 

Thank you in advance!

1 ACCEPTED SOLUTION
rsbin
Super User
Super User

@faustusxanthis ,

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.

rsbin_1-1665505327613.png

Then you can create all of your Visuals using this table.

Hope you can get this to work for you.

Regards,

 

View solution in original post

2 REPLIES 2
rsbin
Super User
Super User

@faustusxanthis ,

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.

rsbin_1-1665505327613.png

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors