I would like to count a column called "ID" based on the criteria of another column "Color". The problem is, I have another table that is associated and has the same "ID", but multiple instances because it has another field called "ROLE".
I would like to count the UNIQUE "ID"s based for "Color" = "RED". It is actually the word "RED", so text.
The table below would yield 3 and 50%. How can I do this? I am trying to use the KPI chart.
| ID | Color |
| WA13 | GREEN |
| WB14 | RED |
| WC15 | ORANGE |
| ZS15 | GREEN |
| DB09 | RED |
The other associated table
| ID | ROLE | USER |
| WA13 | MANAGER | USER1 |
| WA13 | SUPERVISOR | USER2 |
| WA13 | ANALYST | USER3 |
| WB14 | ANALYST | USER3 |
| WB14 | MANAGER | USER4 |
| WC15 | SUPERVISOR | USER5 |
| WC15 | MANAGER | USER6 |
| WC15 | SECRETARY | USER7 |
| ZS15 | MANAGER | USER4 |
| ZS15 | ANALYST | USER8 |
| ZS15 | SUPERVISOR | USER2 |
| ZS15 | SECRETARY | USER9 |
| DB09 | SECRETARY | USER10 |
| DB09 | SECRETARY | USER11 |
| DB09 | ANALYST | USER12 |
