I'm having an issue with Excel Pivot Table recognizing a column as text instead of numbers.
Background:
I have a Main Table (Main_Tbl) with a column ("Net Widgets") that calculates the variance between two other columns (Widgets Sold vs Returned). However, the formula returns a blank cell instead of a 0 if there is no value in either column. The reason for this, is because I have an AGGREGATE function averaging column A (ignoring hidden, blanks and errors), so the average changes depending on other filtering applied to the table. I also don't want to include these blanks as zeros in the average because some stores may not sell widgets during the time period.
Problem:
The problem occurs because I also have a pivot table referencing Main_Tbl, also averaging Net Widgets. However, when there are blank cells present in this column, the pivot table seems to recognize this as text instead, and simply removes the field altogether after a refresh.
In the screenshot linked, the formulas are as follows:
C1: =IFERROR(AGGREGATE(1,3,C$3:C$212),0)
C3: =IFERROR(D3-E3,"")
D3: =IF(ISERROR(VLOOKUP(A3,Ref_Table[Store ID],1,FALSE)),"",SUMIF(Ref_Table[Store ID],A3,Ref_Table[Widgets Sold]))
E3: =IF(ISERROR(VLOOKUP(A3,Ref_Table[Store ID],1,FALSE)),"",SUMIF(Ref_Table[Store ID],A3,Ref_Table[Widgets Returned]))
The screenshot is what is visible PRIOR to refresh. After refresh, the "Average of Net Widgets" column in Pvt_Tbl disappears because of the blanks in row 5 ("CAN003").
I believe my issue is similar to this: Excel 2010: if( , , "") not treated the same as blank for pivot table group by date
I have no idea how to resolve my problem with the pivot table, while maintaining my AGGREGATE function in my original table.
Best Answer
I have a solution for you if you're fine with creating a duplicate NetWidget Calculated column in the Data Model.
For some reason, I was not able to exactly duplicate your issue w.r.t the following:
I have created an Excel with both these scenarios as well as another sample scenario. While using the Data Model, I have create a calculated column named "NetWidget - calc" with formula
=IF(Main_Tbl[NetWidget]="",BLANK(),Main_Tbl[NetWidget])which I was able to convert to a number and use in pivot table.Please find the excel at the following location.
https://drive.google.com/open?id=1jtQQQvrx3W6r5iyO8v3FevC7y-robvNz
Screenshot below:
Please let me know if it doesn't work.