Forum Discussion
Audit overall score
Hello! My data looks like that. I Have different Audits, based on 2 areas and around 5 locations. Each location has it own questions. All questions are answered pass/fail. The questions for each audit are collected in single rows, with a unique Audit ID for each completed Audit.
What I need is a way to show a percentage of completion by audit, location, and area. Also to be able to show a weekly trend of each Audit by date and location.
So right now everything I try to do separates the data for each question, but I need to get the info per Audit answered summarizing the results of each question for that audit.
Anonymous ,
My recommendation is to create a new Calculated Table. Call it "AuditSummary".
First step is to Group By Audit ID and then create columns to calculate # of Questions asked, # of Passes and # of Fails.
AuditSummary = SUMMARIZECOLUMNS( AuditDetail[Audit ID], "Questions", COUNTA( AuditDetail[Question] ), "Pass", CALCULATE( COUNTA(AuditDetail[Response] ), Filter(AuditDetail, AuditDetail[Response] = "Pass" )) + 0, "Fail", CALCULATE( COUNTA(AuditDetail[Response] ), Filter(AuditDetail, AuditDetail[Response] = "Fail" )) + 0)You should get a table looking like this.
Audit IDQuestionsPassFail
1 3 3 0 2 3 2 1 3 3 0 3 Once you get a table looking like this, you should be able to carry on and get the data you need at the Audit level as opposed to the DetailedQuestion level.
Using AuditID, create a relationship 1:many between AuditSummary and AuditDetail.
Hope this gets you pointed in the right direction
Good Luck and regards,
3 Replies
- rsbin
Community Champion
Anonymous ,
Please post your sample data as a table not as a picture.
I should then be able show you a couple of steps that I would take to get you started.
Regards,
Thank you for your time! Here´s the data as a table. Note that I´m not importing my data to BI from Excel. Instead, the data is being collected from a Sharepoint since the Audits are being made through a PowerApp.
Audit ID Area Location Question Response 1 R1 Bank Is it Ok? Pass 1 R1 Bank Is it on time? Pass 1 R1 Bank Is it clear? Pass 2 R2 Shop Is it red? Pass 2 R2 Shop Is it blue? Fail 2 R2 Shop Is it green? Pass 3 R1 Shop Is it red? Fail 3 R1 Shop Is it Blue? Fail 3 R1 Shop Is it Green? Fail - rsbin
Anonymous ,
My recommendation is to create a new Calculated Table. Call it "AuditSummary".
First step is to Group By Audit ID and then create columns to calculate # of Questions asked, # of Passes and # of Fails.
AuditSummary = SUMMARIZECOLUMNS( AuditDetail[Audit ID], "Questions", COUNTA( AuditDetail[Question] ), "Pass", CALCULATE( COUNTA(AuditDetail[Response] ), Filter(AuditDetail, AuditDetail[Response] = "Pass" )) + 0, "Fail", CALCULATE( COUNTA(AuditDetail[Response] ), Filter(AuditDetail, AuditDetail[Response] = "Fail" )) + 0)You should get a table looking like this.
Audit IDQuestionsPassFail
1 3 3 0 2 3 2 1 3 3 0 3 Once you get a table looking like this, you should be able to carry on and get the data you need at the Audit level as opposed to the DetailedQuestion level.
Using AuditID, create a relationship 1:many between AuditSummary and AuditDetail.
Hope this gets you pointed in the right direction
Good Luck and regards,