Calculation errors when using nested SUMX vs. naturalleftouterjoin

haganistic — Sun, 18 Jul 2021 16:04:05 GMT

Hi,

Sorry for long post but I am trying to build up my dax knowledge by really focusing on row context and iterator functions. As I was trying to replicate the virtual tables that are created when using nested iterator functions I came across some interesting discrepancies which hopefully someone can help me understand.

Please note that these queries are meaningless and I know there are more efficient ways to write these queries. I am focused on why combining different tables (but not changing the rows, filter context, context transition,etc) different results (only off on the rounding).

I am using the Adventure Works 2020 model for my data and I start with the following query in DAX Guide:

EVALUATE

{

format(

SUMX (

customer,

SUMX (

RELATEDTABLE ( sales ),

SUMX (

RELATEDTABLE ( 'Product' ),

'Product'[list price] * Sales[Unit Price] * sales[Order Quantity]

)

),"$#,##0.00000")

}

Dax Guide Result: $209,586,475,652.57000 (emphasis added by me)

When I look at the Vertipaq try to see how these nested sumx tables are being formed, I see the below:

SET DC_KIND="AUTO";

SELECT

'Customer'[CustomerKey], 'Product'[ProductKey], 'Internet Sales'[Order Quantity], 'Internet Sales'[Unit Price]

FROM 'Internet Sales'

LEFT OUTER JOIN 'Customer' ON 'Internet Sales'[CustomerKey]='Customer'[CustomerKey]

LEFT OUTER JOIN 'Product' ON 'Internet Sales'[ProductKey]='Product'[ProductKey];

Based on my limited understanding, I used the NATURALLEFTOUTERJOIN() function to try visualize the table. I created the below query in DAX Guide and Excel (using the tabular services trick to connect to Power BI then use the pivot table to create a table and then insert the DAX into the table).

EVALUATE

NATURALLEFTOUTERJOIN(

NATURALLEFTOUTERJOIN(customer,sales),'Product')

Checking the vertipaq enging I see it performs the similar left outerjoin as the original sumx query (see below).

LEFT OUTER JOIN 'Customer' ON 'Internet Sales'[CustomerKey]='Customer'[CustomerKey]

LEFT OUTER JOIN 'Product' ON 'Internet Sales'[ProductKey]='Product'[ProductKey];

However, when I try to validate the result of the original sumx function by either either manually multipling and sum up the columns or applying sumx function to that newly created table, I get a different answer:

Query 1:Dax Guide Result: $209,586,475,652.53600

Query 1: Excel Result: $209,586,475,652.53600

Original SUMX Result:$209,586,475,652.57000

Variance between original SUMX result and Query 1: ($0.03366)

I know the variance is small, but I'm totally confused why I have one?

When I try to further simplify the query and remove the customer table as its not really doing anything other than confusing things, unfortunately things get a bit more confusing. For example:

EVALUATE

{

format(

SUMX (

'Product',

SUMX (

RELATEDTABLE ( Sales),

'Product'[list price] * Sales[Unit Price] * sales[Order Quantity]

)

,"$#,##0.00000")

}

Result= $209,586,475,652.56900

Original SUMX Result:$209,586,475,652.57000

And if I switch the order of the tables (Product and Sales)

EVALUATE

{

format(

SUMX (

Sales,

SUMX (

RELATEDTABLE ( 'Product'),

'Product'[list price] * Sales[Unit Price] * sales[Order Quantity]

)

,"$#,##0.00000")

}

Result :$209,586,475,652.57000

Original SUMX Result:$209,586,475,652.57000

Similar to above, when I try to visualize the two tables using the naturalleftouterjoin I get a different result from both the 3 table query or the original sumx query

EVALUATE

NATURALLEFTOUTERJOIN (Sales,'Product' )

Dax Guide Result: $209,586,475,652.49300

Excel Result: $209,586,475,652.49300

Original SUMX Result:$209,586,475,652.57000

And if I switch the order of the tables

EVALUATE

NATURALLEFTOUTERJOIN(sales,'Product')

Excel: 209,586,475,652.43800

DAX Editor:$209,586,475,652.43800

Original SUMX Result:$209,586,475,652.57000

I do notice something odd in dax studio when I hover over certain values, I see some extra decimals that I guess could add up over many rows to produce the extra rounding impacts:

For example, a value that shows as 3.14646 changes to 3.146460000000003

While I recognize it is a small variance, I'm still interested why there is any variance at all? Does the sumx have a rounding limitation / logic? Or does the naturalleftouterjoin() somehow add extensions to numbers which then changes the overall number? Also why does the table order and number of the joins change the results when the rows (with the relevant data) remain the same?

Hoping someone knows the right answer 🙂

Re: Calculation errors when using nested SUMX vs. naturalleftouterjoin

daxer-almighty — Tue, 20 Jul 2021 20:00:52 GMT

Small differences add up and you're dealing with imprecise numbers - floats. That's normal.

Re: Calculation errors when using nested SUMX vs. naturalleftouterjoin

haganistic — Wed, 21 Jul 2021 14:02:11 GMT

Hi Daxter, thank you for the perspective and reference. Is there a way to ensure we deal with only precise numbers without floats?

Re: Calculation errors when using nested SUMX vs. naturalleftouterjoin

daxer-almighty — Wed, 21 Jul 2021 19:56:40 GMT

You can use the currency type (4 decimal places) but if you divide one number by another you'll still be dealing with rounding.

topic Calculation errors when using nested SUMX vs. naturalleftouterjoin in DAX Commands and Tips

Calculation errors when using nested SUMX vs. naturalleftouterjoin

Re: Calculation errors when using nested SUMX vs. naturalleftouterjoin

Re: Calculation errors when using nested SUMX vs. naturalleftouterjoin

Re: Calculation errors when using nested SUMX vs. naturalleftouterjoin