Forum Discussion
getpivotdata to fill out table
I am trying to use the numbers on the right as a key to get the value from the matching "Dinstinct Count of Rma Number" in the pivot table. I am not sure about how to use this function and the videos I have watched have not helped. I also want it to output a zero, but I should be able to do that with an isna()
Here are the pivot table fields if that helps.
Any help would be appreciated.
For such setup
it could be
=IFERROR( GETPIVOTDATA("[Measures].[Distinct Count of RMA Number]",$G$3,"[Table1].[Day]","[Table1].[Day].&[" & [@Day] & "]"), 0 )I am trying to replicate what you did for your table and for some reason my pivot table is outputting the date like this
My data looks like this:
Also my table isn't pulling the numbers when using the code you gave.
=IFERROR( GETPIVOTDATA("[Measures].[Distinct Count of Rma Number]",$B$4,"[Table4].[Day]","[Table4].[Day].&[" & [@Day] & "]"), 0 )Any ideas as to what I am doing wrong?
I also can't seem to attach my excel file.
Step by step
- we add PivotTable as
In Power Pivot add column with Days
Be sure this setting in Options is On
Create table with all possible days
Within table in any cell start from = and click on any cell in Distinct Count of PivotTable
Enter. Formula like
=GETPIVOTDATA("[Measures].[Distinct Count of Rma number]",$H$6,"[Table1].[Day]","[Table1].[Day].&[12]")appears.
Now let modify this
part of it on
Wrap formula with IFERROR, finally
=IFERROR( GETPIVOTDATA("[Measures].[Distinct Count of Rma number]",$H$6,"[Table1].[Day]","[Table1].[Day].&[" & [@Day] & "]"), 0 )In Power Pivot add Year and Month columns if they were not created initially
=YEAR(Table1[Date]) =FORMAT(Table1[Date], "mmm" )Add slicers or filters to PivotTable
Here we are
