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.
Bookmarks