cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User
Super User

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 

6 REPLIES 6
Super User
Super User

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

Super User
Super User

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 

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

Super User
Super User

Re: Summing the values from multiple columns

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

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.