I have 2 DAX expressions that do the same thing except in one product caculation in 1000's

[JimmySXS]

Hi,
First off I am learning DAXand using data sources found on the web to practice. I have created a model and was practicing my DAX and wrote 2 expressions that I thought did the same thing.
The first one 
1 Total Order Sales = SUMX(OrderLine, OrderLine[Sales] * OrderLine[Quantity])
then the next 2 to get the same value
2 Sales After Discount = SUMX(OrderLine, [Total Order Sales] *(1-OrderLine[Discount]))
3 Sales After Discount 2 = SUMX(OrderLine, (OrderLine[Sales] * OrderLine[Quantity]) * (1-OrderLine[Discount]))

It worked for 1000's of lines then not on 1 and I don't know why? Also I cannot find anywhere to attach the PBIX & xslx files I'm working from sorry.

Jim

[JimmySXS]

Hi Guys thanks for your replies but I don't think the data is duplicated. Here is one of the products that is incomplete

[Anonymous]

Thanks for the reply from LYPowerBI  and johnt75  , please allow me to provide another insight:

Hi, @JimmySXS 
Thanks for reaching out to the Microsoft fabric community forum.

Yes, as johnt75 mentioned and LYPowerBI verified, if the data itself contains duplicate rows, it can lead to inconsistent calculation results. The SUMX function inherently involves iteration. Below is a screenshot from the official documentation:

For more details, please refer to:

https://learn.microsoft.com/en-us/dax/sumx-function-dax#remarks

https://learn.microsoft.com/en-us/dax/dax-glossary#iterator-function

Here is my testing process:

1.This is my sample data:

The red box highlights the obvious duplicate data.

2..Here are the final results:

The results are inconsistent.

4.To modify this, you can try applying a DISTINCT() function to the Sales After Discount measure or the Total Order Sales measure before referencing the table. The final result is as follows:

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LYPowerBI]

[johnt75]

I think that there are duplicate rows in your data, probably in the order line table but possibly in one of the tables order line is related to.

In your first sales after discount measure you are iterating over order line and then performing context transition by calling the [Total Order Sales] measure. That will put all copies of the duplicate rows into the filter context and you will get the sum of all those duplicates multiplied by the discount.

In the second version you are not forcing context transition, you are only iterating over the table once, so each duplicate row will only be counted once, not once per the number of duplicates.

On the attachment point, you can put files on Google Drive, OneDrive or similar and share them from there.

[JimmySXS]

I tried to create a link like you suggested.

Download files 

[johnt75]

I created a [Diff] measure to show when the 2 measures differed, and you can see that there is a difference on e.g. 29 August 2012. Looking at the order lines for that day, using

DEFINE
VAR _Date = TREATAS( { DATE( 2012,8,29) }, 'Calendar'[Date] )

EVALUATE
CALCULATETABLE(
	OrderLine,
	_Date
)
ORDER BY OrderLine[OrderID], OrderLine[ProductsID]

you can see that order ID 5907386 has a duplicate line for product 29. I'm sure there will be similar duplications for the other dates.

[JimmySXS]

Hi,

Thank you for your quick reply. I stillcannot find a place to attach my files and now I cannot cfind anywhere to change my password as I forgotton it. 

JimmySXS

[Anonymous]

Hi, @JimmySXS 
 

Thank you for your prompt response.

If you need to upload a PBIX file, I recommend using SharePoint or GitHub and attaching the file link in your reply. Please ensure that any sensitive information is removed.

Here are some links on how to upload data:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards,

Leroy Lu