cancel
Showing results for
Did you mean:
heytherejem 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

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
Baskar Super Contributor

Re: Summing the values from multiple columns

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

heytherejem 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 :robotindifferent Baskar 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

heytherejem 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

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

Re: Summing the values from multiple columns

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.

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. 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. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 331 members 3,314 guests
Recent signins:
• aimee_fernandez • wimsangers • zimmerlid • Marc76 • avanroij • vik0810 • SHAMAK • Bluewolf26 • robmarsh • mattmiller • Cquatre02 • andyhdsn1 • gregr_data • jochen_spluegen 