Pass Fail Inspection Score Average?

[athren]

I am new to PowerBi, I work in a Hospital within the Housekeeping department. In essence we are trying to calculate inspection scores for our visual room inspections. In the past I created an Excel spreadsheet that we would just input our data from our paper inspections over for it to calculate those scores. However we recently received cellphones and excel on a mobile phone is possible but very user unfriendly. 

So it was brought to my attention of using our sharepoint as our datasource and the microsoft list app to do the inspections on. The issue I have now is achieving the formulas to give me a percentage score for each room. As you can see in the image below, there 16 items we are inspecting, the title is the room that is being inspected. I know we have sections called Corrected, I am unsure if the complicates the equation more so. 

I thought I could do something that would Count the number of Pass in a row for one column, one to do the same for corrected and fail. And then do something similar to excel where I can just divide the number of items passed divided by the number of items inspected. But I'm not working with Cells in excel, PowerBi seems column centric and it generates a lot of columns that I don't know how to exclude. 

If someone can just show me a formula that would work, even just a small test sample With Ceilings, Walls and Floors only I am sure I can just add all the other columns unless it doesn't work that way. 

But my table that holds for all of these columns is called Visual Inspections, I been beating my head for weeks trying to solve this. So any help or guidance would be appreciated, if not that is okay. I am watching a lot of Youtube videos so eventually something will click.

For example using a smaller data pool I am trying to achieve something like this....

Ceiling	Windows	Walls	Door	Floor	Pass Count	Fail Count	Corrected Count	1st time Inspection Score % (Pass vs Fail & Corrected)	2nd time Inspection Score % (Pass & Corrected vs Fail)
PASS	PASS	FAIL	PASS	PASS	4	1		80%	80%
PASS	PASS	CORRECTED			2		1	66.6%	100%
PASS	FAIL				1	1		50%	50%

But I am open to any suggestions for a more efficient method, as this is basically what I did on excel.

[athren · Accepted solution]

When all else fails and I ask for help, I ended up figuring it out the very next day. However if other people find themselves in the same situation this is what I did....

[sidoacm]

Hi, I am trying to calculate the same formula for my data.  Did you write a measure for each column [Cabinets], [Ceilings], etc... and then plug that into the IF function? My version of your solution is not working.

Thank you!

[v-easonf-msft]

Hi, @athren 

It's pleasant that you can share your sloution to us, people who meet a similar problem will be very happy with it.

Please accept your reply as solution to close this thread, so that other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason