Forum Discussion
Audit overall score
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,
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 |
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,