cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem Member
Member

Summing the values from multiple columns

Hello,

 

I have several columns of data which old a 'PASS' 'FAIL' or 'N/A' value.

 

I need to count per row the total 'PASS' values within all the columns.


So for example if Row A contained 12 PASSES and 6 FAILS, the new measure would show 12.

 

I can't seem to get a COUNTX to work but i'm probably not doing it right. 


Am I able to sum up a specific text value from multiple columns using DAX? 

1 ACCEPTED SOLUTION

Accepted Solutions
Baskar Super Contributor
Super Contributor

Re: Summing the values from multiple columns

@heytherejem

 

Here we have to create measure for each column. 

 

Pass Count = 

 calculate(counta( TableName(Column1)), TableName(Column1)="Pass") +

 calculate(counta( TableName(Column2)), TableName(Column2)="Pass") +

 calculate(counta( TableName(Column3)), TableName(Column3)="Pass") +

 calculate(counta( TableName(Column4)), TableName(Column4)="Pass") +

 calculate(counta( TableName(Column5)), TableName(Column5)="Pass") 

 

 

 

Note :

Replace Tablename and column name 

View solution in original post

6 REPLIES 6
Baskar Super Contributor
Super Contributor

Re: Summing the values from multiple columns

@heytherejem   can you please share some of sample data with expected result.

heytherejem Member
Member

Re: Summing the values from multiple columns

Sure @Baskar see attached screenshot. I guess my post wasn't as self-explanatory as I thought :robotindifferentSample.PNG

Baskar Super Contributor
Super Contributor

Re: Summing the values from multiple columns

@heytherejem

 

Here we have to create measure for each column. 

 

Pass Count = 

 calculate(counta( TableName(Column1)), TableName(Column1)="Pass") +

 calculate(counta( TableName(Column2)), TableName(Column2)="Pass") +

 calculate(counta( TableName(Column3)), TableName(Column3)="Pass") +

 calculate(counta( TableName(Column4)), TableName(Column4)="Pass") +

 calculate(counta( TableName(Column5)), TableName(Column5)="Pass") 

 

 

 

Note :

Replace Tablename and column name 

View solution in original post

heytherejem Member
Member

Re: Summing the values from multiple columns

Thanks @Baskar, it looks like this is summing columns, rather than rows? Each row in my dataset is an audit result,

So I actually don't care about the column totals at all, just the row totals. 

Therefore I figured I would have to create a new calculated column either in DAX or M to achieve this...

Baskar Super Contributor
Super Contributor

Re: Summing the values from multiple columns

@heytherejem use the same formula to create calculated column, it will resolve your problem.

Highlighted
Community Support Team
Community Support Team

Re: Summing the values from multiple columns

@heytherejem,

 

You may select Unpivot Columns in Query Editor first.

https://community.powerbi.com/t5/Desktop/matrix-to-table-help/td-p/329415

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 331 members 3,314 guests
Please welcome our newest community members: