Excel Pivot Table recognizing blank cells ("") in column as text not numbers

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:

  1. The formula D3-E3 did not return error, it returned 0 when blank. So I used IF($D3="","",$D3-$E3)
  2. When I created a pivot table directly from Main_Tbl (Not from Data model), the average Netwidget column was working properly.

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: If blank Pivot table

Please let me know if it doesn't work.