Log In
Register
Home
Power Pivot
September 2, 2020
Offline
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'?
See Answer
July 16, 2010
Offline
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
Power Pivot
September 2, 2020
Offline
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
Power Pivot
September 2, 2020
Offline
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
July 16, 2010
Offline
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
Answers Post
1 Guest(s)
