Multiple columns containing PASS / FAIL how to show a list of highest to lowest

[wereleopard]

Hello

I have multiple questions on a sharepoint list that can be a PASS or FAIL and need to show which of these questions has the highest number of 'FAIL's 

E.G

Source data:

Week	Question 1	Question 2	Question 3	Question 4
1	PASS	PASS	PASS	PASS
1	FAIL	PASS	PASS	PASS
1	PASS	PASS	FAIL	PASS
1	PASS	PASS	FAIL	PASS
1	PASS	PASS	FAIL	PASS
1	PASS	PASS	PASS	PASS
1	PASS	PASS	PASS	PASS
1	PASS	PASS	PASS	PASS
1	PASS	PASS	PASS	FAIL
1	PASS	PASS	PASS	FAIL
1	PASS	PASS	PASS	PASS

The ideal report visual would be:

Question	Fail Count
Question 3	3
Question 4	2
Question 1	1
Question 2	0

Can anybody help as to how I can build this table?

Thanks

[Bibiano_Geraldo]

HI @wereleopard ,

First lets do some transformation in power query:

Step 1: Unpivot your data

In Power Query Editor, select the week column, right-click and choose Unpivot Other Columns as shown bellow. 

Now your data should look like this:

Now your can rename this columns for what you want and close and apply.

Step 2: Create a measure to count FAILs by this DAX:

Fail Count = 
CALCULATE(
    COUNTROWS('YourTableName'),
    'YourTableName'[Value] = "FAIL"
)

Step 3: Create a table visual:

1. Add a new table visual to your report.
2. Drag the Attribute field (which contains the question names) to the Rows section.
3. Drag the Fail Count measure to the Values section.

Step 4: Show items with no data:

Step 4: Click on the column header for Fail Count to sort the table by the number of FAILs in descending order.

I hope this satisfies your need, if yes, please mark it as solution and give a Kudo

[johnt75 · Accepted solution]

In Power Query select the week column and then choose Unpivot Other Columns from the Transform ribbon.

You can then create a measure like

Fail Count = CALCULATE( COUNTROWS( 'Table' ), 'Table'[Value] = "FAIL" )

[wereleopard]

Wonderful - thank you!