I have an excel data scource in OneNote for Business containing a table of MANAGERS, their STAFF names (so the manager name occurs multiple times) and their staff Skill Score Criteria (SKILL_1, SKILL_2 etc). So staff access the tool, select their manager and for each of 10 skill types, they score themselves between 1 and 7 depending how proficient they think they are in that particular skill type. They do this twice a year and we monitor any uplift in skill score which helps identify any training needs etc.
I now realise that PA will not support a table that contains formulae, so I can't perform some tasks in excel as I wanted to. So one thing I want to do is offer a manager whi accesses theh tool the option to select their own name from a DD list (I already have this DD list populated) and upon selection, they will see 1) a list of their staff and their skill scores (I can do this using a data table okay), but, I also want to provide an average skill score for each Skill Type for that manager's team and maybe display that as a chart.
I cannot figure out how to AVERAGE a Skill Type colum after filtering the table by the MANAGER's name. So I just want to get a Skill Score average of the staff who have the same manager. Hope that makes sense...
I've tried a variation of Filter(), Sum(), Average(), Search() but I am stuck.
Can anyone point me in the right direction please?
Many thanks
