Dear Team,
I have a table in the Power Pivot Data Model. I have created two Calculated Columns that are supposed to calculate the Line Item COGS for each sale. The two Formulas are identical, except for the order in which multiplication is done. For some rows in the table, the two formulas yield different answers. If I try the same two formulas in an Excel Sheet with Excel Spreadsheet Formulas every line yields the same answer. Why is it that in a DAX Calculated Column a formula would yield a different number simply by changing the order of how the numbers are multiplied?
Here is Calculated Column Number 1:
LineCOGS01 = ROUND(RELATED(dProducts[StandardCost])*fSales[PercentOfStandardCost]*fSales[Units],2)
Here is Calculated Column Number 2:
LineCOGS02 = ROUND(fSales[Units]*fSales[PercentOfStandardCost]*RELATED(dProducts[StandardCost]),2)
The only difference between the two is for #1 we mutliply Cost*Percent*Units, and for #2 we multiply Units*Percent*Cost
The actual numbers used in the calcualtion are:
LineCOGS01 = 8.25 * 0.953 * 131, rounded = 1029.96
LineCOGS02 = 131 * 0.953 * 8.25, rounded = 1029.95
If I export the table to an Excel Sheet and use Excel Spreadhseet Formulas I do not get this error. Here are the formulas I used:
ExcelLineCOGS03 = =ROUND([@StandardCost]*[@PercentOfStandardCost]*[@Units],2) = 1029.95
ExcelLineCOGS04 = =ROUND([@Units]*[@PercentOfStandardCost]*[@StandardCost],2) = 1029.95
Here is a link to the file that contains the two Calcualted Columns with this "Order of Multiplying Difference Error": https://people.highline.edu/mgirvin/AllClasses/100/CalcualtedColumnOrderOfMultiplyingDifferenceError...
In the Data Model, the table named fSales contains the two Calculated Columns named LineCOGS01 and LineCOGS02.
Any ideas?
Sincerely, Mike Girvin
