We have data of our colleagues with regarding to their skills (competences, knowledge, tools),
including maturity levels (imported from SharePoint lists)
In one pivot table we want to show which persons satisfy the requirements,
but in another we want to show (per person) which skills have to or can be developed.
What is the easiest way to create that pivot, in fact showing data of 'missing records'?
Hi Maarten,
Welcome to our forum! If you can use regular PivotTables you can simply edit the Field settings and check the 'Show items with no data' box.
Otherwise, for Power Pivot you need to create a measure that returns zero where there are no requirements met. You can do this with a formula like so:
=IF(ISBLANK(COUNT(Skills[Competency])),0,COUNT(Skills[Competency]))
See file attached.
Mynda
Hi Mynda,
Thanks for this 'push forward' (at least in thinking) !
What we would like is to filter the Power Pivot to show only the 0-records.
Is there a trick to define a measure that doesn't show up in the Values section?
kind regards,
Maarten
In the 'new' Power Pivot we want to show only the competencies a person has to develop.
I tried several ways to change your measure into a True/False or Text-type.
But I still haven't succeeded in making the right way to filter the Pivot.
Do you have any clue?
kind regards,
Maarten
Hi Maarten,
Change the layout so that the rows contain Employee and then competency. Then select one of the competency row labels in the PivotTable > click on the filter button > Value filters > equals 0.
Mynda
Ok, thanks.
It's just thinking another way , but we can deal with that!
kind regards,
Maarten
