cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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

1 REPLY 1
Highlighted
Helper I
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?

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

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

Using the Community

Using the Community

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

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors