However, we have duplicate order #'s in our system...Ex: Order number 123456 happened in 2015 and 2019. When I use this DAX calculation (bottom of page), it is summing the sales for the 2019 order and 2015 order. I have a filter saying order date has to be greater than 01/01/2017 so I don't care about the 2015 sale but it still is summed in the formula. Any help?
Sales Order Total Value Containing Items = CALCULATE(SUM('Sales Detail'[Sales]),ALL('Sales Detail'),(VALUES('Sales Detail'[Sales Order #])))
Thanks cmcmahan. I should've been more clear in that I have a filter containing items as well. Using your suggestion, I get the Sales $ for just those items in the order and not the entire Order $ value.
This is closer and it works well without the filter but I need that filter on because I want to see total order value of orders containing at least one item from Item Class 1 or 2 (my example in SQL at the bottom). In the demo screenshot below, I would want line 1 to show Sales 100 (which it does) but Measure of 600 because that's the total Sales Order Total $ of Order ID 1. It could be possible that with filters, my ask is not quite possible.
This SQL statement is what I need to recreate. It generates a list of Distinct Sales Order #'s based on the Item Classes of 1 and 2. Then it pulls the total sales of that Sales Order # (not just items from Item Classes 1 and 2).
SELECT * FROM (SELECT DISTINCT DTL.SALES_ORDER_# FROM SALES AS DTL WHERE DTL.INVOICE_DATE >= '07/11/2019' AND DTL.ITEM_CLASS IN ('1','2') )DOCS
(SELECT DTL.SALES_ORDER_#, SUM(DTL.SALES) AS SALES FROM SALES AS DTL WHERE DTL.INVOICE_DATE >= '07/11/2019' GROUP BY DTL.DOCUMENT_NUMBER )SALES ON DOCS.DOCUMENT_NUMBER = SALES.DOCUMENT_NUMBER
This is nearly perfect but now the outstanding issue is the duplicate orders in our system. The latest DAX is providing almost exactly what I need but we have orders with the same Sales Order # in 2015 as 2019 so it's calculating the sum of both orders. This DAX is not including the second SQL query where the date is restricted to >=7/11/2019 to eliminate summing those previous duplicate SO $'s.