I need to create a CX Dashboard visualization of data collected as part of hand hygiene rounding in a side by side question type. The question has the opportunity for auditors to provide up to 5 observations at a time (we don’t always have data for all 5 observations). Each observation has a rating of compliant or non-compliant and the role of the associated individual for each observation (e.g., compliant nursing staff member who washed hands after interacting with a patient). The role dropdown includes a set of 6 different role options. Here’s how the questions is built:

In an ideal world, we would like to have a compliance rating by role calculated across all observations for a specific domain (e.g., After interacting with patient/client), but I can’t seem to figure out how to do that. I’ve calculated compliance ratings in aggregate across all observations, but don’t know how to fold in breaking down the aggregate compliance ratings by role. 

Does anyone have any suggestions on what I could do for this?? What would be most helpful is knowing how the different roles breakdown by compliance across different domains of hand hygiene assessment. For example, for the domain listed in the screen grab above, what is the breakdown of compliance (observed compliance/total observations ocompliant+non-compliant]) for Behavior Psych, for Therapy, for Mental Health, etc.?