DAX Power Pivot does order matter in multiplication

[dicken]

Hi,
I have posted a similar query on Excel Forum :

https://www.excelforum.com/office-365/1383891-dax-power-pivot-totals-using-sumx-and-related.html#post5704765

My question relates to the order you put things in a measure or calculated column where multiplication takes place
so;
t

ryone :=
SUMX (
    Sales,
    Sales[Sales]
        * ( RELATED ( 'Item'[Price] ) - RELATED ( 'Item'[Cost] ) )
        * ( 1 - RELATED ( Customer[Discount] ) )
)

Which works, but only two of the possible six permutations work, both ending on discount, the other four fail,
the other way is to use a summarized sales table ;

tryfour :=
SUMX (
    SUMMARIZE ( Sales, 'Item'[Cost], 'Item'[Price], Customer[Discount] ),
    ( 'Item'[Price] - 'Item'[Cost] ) * ( 1 - Customer[Discount] )
        * CALCULATE ( SUM ( Sales[Sales] ) )
)

Can someone explain why this happens and what the 'rules' are ?

Richard

 

[Matt Allington]

Multiplication order works the same way as mathematical rules. Order is not important

the first measure is inherently inefficient. i have a general guide. If you are using RELATED, in a measure, it’s probably not the best formula.

the second version is much better, because you are first generating the smallest possible virtual table, and are iterating over that.

 

[dicken]

Multiplication order works the same way as mathematical rules. Order is not important

the first measure is inherently inefficient. i have a general guide. If you are using RELATED, in a measure, it’s probably not the best formula.

the second version is much better, because you are first generating the smallest possible virtual table, and are iterating over that.

Yes I accept the efficiency argument, but I am definitely getting different results ;
so if we take each part sales = s , price = cost = c and 1-discount = s
I have the following results ;;
scd = true
sdc = false
cds = false
csd= true
dsc = false
dcs = true

I have also put this measure on two sheets once on the sales table ;

SUMX(Sales,Sales[Sales]*(1-RELATED(Customer[Discount])*(RELATED('Item'[Price])- RELATED('Item'[Cost]) ) ) )
= 29.06 , which is incorrect

and again on the Customer table :

SUMX(Sales,Sales[Sales]*(-RELATED(Customer[Discount])*(RELATED('Item'[Price])-RELATED('Item'[Cost]) ) ) )
= -11.04,
I'd be interested in your comments, as I may be doing soemthing really stupid,
I've attached a link to a copy of the file on onedrive;

28022022version2 .xlsx

Microsoft Excel Workbook

1drv.ms

Richard

 

[dicken]

Multiplication order works the same way as mathematical rules. Order is not important

the first measure is inherently inefficient. i have a general guide. If you are using RELATED, in a measure, it’s probably not the best formula.

the second version is much better, because you are first generating the smallest possible virtual table, and are iterating over that.

In response to my previous reply, 'yes' was doing something stupid, I had my bracketing wrong on the discount;
I'd made a mistake and repeated it, I kept forgetting a classing bracket . I have a habbit of this sort of thing, I once failed a maths exam not because my working out was wrong, I'd written down the wrong opening number .

Richard