Forum Discussion
How to Transform Pass/Fail Question Data
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.
Hi egghead - In Power Query editor, transform tab--> Unpivot the Question Columns
To reshape your data, you will need to unpivot the Question columns:
- In the Power Query Editor, select the Site Name, Date, and Assessor columns.
- 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!!
3 Replies
Hi egghead - In Power Query editor, transform tab--> Unpivot the Question Columns
To reshape your data, you will need to unpivot the Question columns:
- In the Power Query Editor, select the Site Name, Date, and Assessor columns.
- 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!!Thank you rajendraongole1 so much for your assistance!!! With your assistance I was able to create the desired report:
