How to Transform Pass/Fail Question Data

[egghead]

I am new to Power BI and I am looking for some help on how to transform Pass/Fail Question data so I can display the responses in a Power BI bar chart. I have an excel file with six columns Site Name, Date, Assessor, Question 1, Question 2 and Question 3. 

My objective is to Transform the data by adding the pass/fail values for each of the Question columns and displaying a bar chart with each of the totals broken down by site name. Ideally, the bar chart would include a Site Name on the X axis and either a stacked bar (Pass and Fail) or two bar values (Pass and Fail) for each of the Sites on the Y Axis.   

[ryan_mayu · Accepted solution]

@egghead 

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

[egghead]

Thank you @rajendraongole1 so much for your assistance!!! With your assistance I was able to create the desired report:

[rajendraongole1 · Accepted solution]

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

[ryan_mayu · Accepted solution]

@egghead 

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