Form relationship between Table column and Table Headers AND multiple columns

[JakobGray]

I have three tables of data held within an excel workbook measuring compliance.

I am trying to build a compliance dashboard that will allow me to see through any sort of visualisation the error rates of employees across a range of categories, however I am encountering numerous issues with this due to the format of the source data.

In essence, I am trying to associate Table A sub category with Table C headers AND be able to use table B as a filter.

The end result I am looking for, is the ability to show by employee a count of how many errors they have across each of the categories and by manager a count of errors across categories.

I cannot for the life of me figure this out, and this is a rather easy task in Spotfire, but I am missing the ability to locate the functionality in Power BI...

I would love any assistance anyone is able to provide.

I have tried transposing table C, this didn't seem to work particularly well as it still didn't allow me to associate the employees in any meaningful way, and all the column headers became a list of applications...

Format of data below.

Table A contains the categories and sub-categories of compliance items:

Category	Sub Category
Wallet Check	License Sighted
Wallet Check	DOB Confirmed

Table B contains the details of the persons undertaking the compliance checks:

Employee	Manager	State	Branch
Person A	Manager 1	NSW	A
Person B	Manager 1	NSW	A
Person C	Manager 2	NSW	A
Person D	Manager 1	NSW	B
Person E	Manager 2	QLD	C

Table C contains the compliance records; being who completed the check and the associated application ID:

Where a name is recorded under the categories, this means it was not completed correctly and is an error.

Application_ID	First Pass %	Second Pass %	License Sighted	DOB Confirmed	Other Item
111111	50%	90%	Person A		Person B
111112	75%	100%	Person B	Person C	Person D

[MFelix]

Hi @JakobGray ,

You need to unpivot the columns on table C not transpose that way you will get the information in the same format, however there is a question about the unpivot is that all the columns that you don't unpivot are repeated and this can cause values to be repeated where you don't want them repeated.

In this case are all of the percentages values also part of your Table A categoy or only the two columns identified?