How to Transform Pass/Fail Question Data

egghead 

select the first three columns and unpivot other columns. then it will be easier for you to do the calculation

Hi egghead - In Power Query editor, transform tab--> Unpivot the Question Columns

To reshape your data, you will need to unpivot the Question columns:

1. In the Power Query Editor, select the Site Name, Date, and Assessor columns.
2. Right-click on the selected columns and choose Unpivot Other column transformation

    

close and apply the changes in power query editor, 

Using dax, create an measure for pass as below:

Total Pass = CALCULATE(COUNT('Table'[Result]), 'Table'[Result] = "Pass")

another measure for fail:

Total Fail = CALCULATE(COUNT('Table'[Result]), 'Table'[Result] = "Fail")

Now you can choose bar chart to display the pass/fail count for each site.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!