cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

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
Highlighted
Resident Rockstar
Resident Rockstar

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
Highlighted
Resident Rockstar
Resident Rockstar

Re: Summing the values from multiple columns

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

Highlighted
Post Partisan
Post Partisan

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

Highlighted
Resident Rockstar
Resident Rockstar

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

Highlighted
Post Partisan
Post Partisan

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

Highlighted
Resident Rockstar
Resident Rockstar

Re: Summing the values from multiple columns

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

Highlighted
Community Support
Community Support

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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors