Hello World,

The formula below was doing a great job at calculating a weighted average, UNTIL an addition of criteria of different size.

=IF(fldSupplierCost=0,fldWeightedPercent,IF(ISBLANK(fldSupplierID),(SUMPRODUCT(tblMar2019[Supplier Cost],--(tblMar2019[Product Line]=tblProductLine[Product Line]),--(tblMar2019[Supplier Cost]>0)))/(SUMPRODUCT(tblMar2019[Claim Cost],--(tblMar2019[Product Line]=tblProductLine[Product Line]),--(tblMar2019[Supplier Cost]>0))),(SUMPRODUCT(tblMar2019[Supplier Cost],--(tblMar2019[Product Line]=tblProductLine[Product Line]),--(tblMar2019[Supplier Cost]>0),--(tblMar2019[SAP]=fldSupplierID))/(SUMPRODUCT(tblMar2019[Claim Cost],--(tblMar2019[Product Line]=tblProductLine[Product Line]),--(tblMar2019[Supplier Cost]>0),--(tblMar2019[SAP]=fldSupplierID))))))

The additional criteria is the several instances of

--(tblMar2019[Product Line]=tblProductLine[Product Line])

tblMar2019 is a table of say 20,000 records

tblProductLine is a table that varies from 1 to 20 or so records

With the additional criteria it throws a #N/A error. Tried array formula - still no go.

Any suggestions would be oh so appreciated.