Nov 13 2020 04:26 AM
Nov 13 2020 08:44 AM
That's how GETPIVOTDATA works. If you don't want that, it's better to use SUMIFS based on the source data. Enter A and B in M3 and M4.
Then enter the formula
and copy or fill to the other cells. See the attached version.
Nov 13 2020 08:49 AM
Nov 13 2020 11:12 AM
Nov 13 2020 12:34 PM - edited Nov 13 2020 12:39 PM
GETPIVOTDATA() doesn't work in such case, it provides data if only it is visible in PivotTable. Alternatively you may work with cube assuming you are at least on Excel 2010, as I remember it's the first which supports data model.
Creating PivotTable add data to data model by checking
Create PivotTable and after that convert it to cube formulas. Now you may take these formulas and convert it to form you need, for example
in H3 it could be
=CUBEVALUE( "ThisWorkbookDataModel", CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[Sum of Aantal dagen]" ), CUBEMEMBER("ThisWorkbookDataModel", ($G$2,$G3) ), CUBEMEMBER("ThisWorkbookDataModel", "[Range].[Week]." &"[" & TEXT(H$1,"General") & "]" ) )
and drag it to the right and down.
Remove generated cube formulas you don't need.
If in parallel you need PivotTable as well, better to create it using data model
Filtering of this PivotTable won't affect cube formulas, they use only connection to data model but not another PivotTables created based on it.
Nov 13 2020 12:39 PM
I am not a great Pivot Table user so I tend to duck out of the PT environment and resort to dynamic arrays.
If you also wish to filter the headings to omit the rows without content the formula starts to get somewhat overcomplicated.
= LET( distinct, UNIQUE(Table1[Medewerker]), totaal, GETPIVOTDATA( "Aantal dagen",PivotTable, "Medewerker", distinct, "Werkzaam",Werzaam), FILTER( distinct, ISNUMBER(totaal) ) )
In either case, the table content is of the form
= IFERROR( GETPIVOTDATA( "Aantal dagen", PivotTable, "Week", Week#, "Medewerker", Medewerker#, "Werkzaam", Werzaam), "-" )
A combination of INDEX/SMALL/MATCH can be used in place of FILTER.