cancel
Showing results for
Did you mean:
Highlighted Helper I

## DAX Calculated Column Order of Multiplying Difference Error

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

Highlighted Helper I

## Re: DAX Calculated Column Order of Multiplying Difference Error

I have also tried this is Power BI. And the Calculated Columns yield different answers just by switching the order of how the numbers are multiplied:

#1 = Cost * Percentage * Units:

LineCOGS01 = ROUND(RELATED(dProducts[StandardCost])*fSales[PercentOfStandardCost]*fSales[Units],2)

#2 = Units * Percentage * Cost
LineCOGS02 = ROUND(fSales[Units]*fSales[PercentOfStandardCost]*RELATED(dProducts[StandardCost]),2)

Here is Power BI file: https://people.highline.edu/mgirvin/AllClasses/100/MultiplyingOrderError.pbix

Any ideas?

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries! Top Solution Authors
Top Kudoed Authors
Users online (2,087)