DAX Calculated Column, Multiplying Order Changes Answer?

[mgirvin]

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

 

[Ozeroth]

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

 

[mgirvin]

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

 

[Ozeroth]

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

 

[mgirvin]

Owen,

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

Sincerely, Michael Girvin

 

[Ozeroth]

You're welcome Mike
All the best,
Owen