DAX Calculated Column, Multiplying Order Changes Answer?

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,236
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi Mike,

The underlying reason for the difference is that the column dProducts[StandardCost] is of type Currency (aka Fixed Decimal Number), while the other columns are of type Decimal.

In particular:

  1. The Currency type stores decimal values with up to four decimal places (actually stored as integers divided by 10,000).
  2. When a Currency value is multiplied by a Decimal value in DAX, the result is a Currency value, resulting in potential loss of precision.
  3. The result is that the order of multiplication of 3 or more numbers matters when Currency and other types are mixed.

I am assuming that DAX multiplication is performed from left to right, so that in your case, the two calculations are as follows
( [C] indicating Currency and [D] indicating Decimal):


  • LineCOGS01 = ROUND ( 8.25[C] * 0.953[D] * 131[D], 2)
    = ROUND ( 7.8623[C] * 131[D], 2 )
    = ROUND ( 1,029.9613[C], 2 )
    = 1,029.96
  • LineCOGS02 = ROUND ( 131[D] * 0.953[D] * 8.25[C], 2)
    = ROUND ( 124.843[D] * 8.25[C], 2)
    = ROUND ( 1,029.9548[C]
    , 2)
    = 1,029.95

Since LineCOGS02 multiplies by the Currency value last, the rounding to 4 decimal places (as a result of conversion to Currency) happens later.

To get the same result from these two formulas (and match Excel), you could change dProducts[StandardCost] to Decimal type.

By the way, I had to look up the detail on behaviour of these different types in this SQLBI article :)
https://www.sqlbi.com/articles/understanding-numeric-data-type-conversions-in-dax/

Regards,
Owen
 
Last edited:
Upvote 0
Dear Owen,

Brilliant, Owen : )

Thank you very much for unearthing the issue here.

However, the implication seems disturbing. It seems to me that in order to not disrupt all of mathematical history (commutative property of multiplication), that when designing Data Models we will have to assign a consistent Data Type for all numbers that might be used for multiplying and dividing.

Is this how you approach designing your Data Models?

Again, thanks a million!

Sincerely, Mike Girvin
 
Upvote 0
You're welcome Mike :)

After re-looking at the SQLBI article, the problem can only arise when Currency & Decimal types are combined in a multiplication or division. All other combinations appear to give results that are at least as precise as the operands.

In models where you want to completely avoid this problem, I would suggest avoiding the Currency type for any column that might be involved in a multiplication/division.

For decimal values, I personally tend to avoid Currency column types and go with Decimal, simply because of Currency's 4 decimal place precision, but this comes at the cost of memory of course.

Regards,
Owen
 
Upvote 0
Owen,

I just have to say thank you again for helping me with this!

Sincerely, Michael Girvin
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top