Calculate Order Value Total ($) When Containing Item used in Filter - Duplicate Order #'s in system

[USABB_Data]

I see the other thread posted here: https://community.powerbi.com/t5/Desktop/Measure-Total-order-value-for-orders-with-item/m-p/322848#M...

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 #])))

[v-lili6-msft]

hi, @USABB_Data 

For that example case, It is not a dynamic way.

Try this formula to create a measure:

Measure = VAR _orders=VALUES(Orders[OrderId]) return
CALCULATE(SUM(Orders[Sales]),FILTER(ALLSELECTED(Orders),Orders[OrderId] in _orders))

and here is a sample pbix file, please try it.

If not your case, please share your expected output.

Best Regards,

Lin

[USABB_Data]

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

  INNER JOIN

  (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

[Cmcmahan]

Try this measure:

OrderTotal = 
CALCULATE( SUM(Orders[Sales]),ALLEXCEPT(ALLSELECTED(Orders), Orders[OrderId]) )

[USABB_Data]

Parameter issue on cmc's latest suggestion:

[v-lili6-msft]

hi, @USABB_Data 

If you try this formula:

Measure = VAR _orders=VALUES(Orders[OrderId]) return
CALCULATE(SUM(Orders[Sales]),FILTER(ALL(Orders),Orders[OrderId] in _orders))

If not your case, please show more expected output with different scenario.

Best Regards,

Lin

[USABB_Data]

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.


  

[Cmcmahan]

Then just add a filter condition for the year to the query.

Measure = VAR _orders=VALUES(Orders[OrderId]) return
CALCULATE(SUM(Orders[Sales]),FILTER(ALL(Orders),Orders[OrderId] in _orders && Orders[Date]>=DATE(2019,7,11)))

[USABB_Data]

Thanks that does work but I want to avoid hardcoding dates because I want the measure to be reausable.  I have the date filter already on my report as well.

[v-lili6-msft]

hi, @USABB_Data 

If possible, could you share your simple sample pbix file for us have a test?

Best Regards,

Lin

[USABB_Data]

I don't have Dropbox but hopefully the screenshots will provide more clarity.

Used your demo and created an Order Date table with a duplicate OrderID of 2 to mimmick our setup (1st screenshot).  Then I filter the Order Date to just 7/11/2019 (2nd screenshot).  I want the measure to now show just 75 because that's the total for the measure on 7/11/2019 and the 5/15/2019 data is irrelevant.

[Cmcmahan]

So then replace the hardcoded value with a measure or expression that returns the date from your date filter. 

Measure = 
VAR _orders=VALUES(Orders[OrderId]) 
VAR _curDate = SELECTEDVALUE(Orders[Date])
RETURN
CALCULATE(SUM(Orders[Sales]),FILTER(ALL(Orders),Orders[OrderId] in _orders && Orders[Date]>=_curDate))

[Cmcmahan]

You may have a filter requiring dates to be greater than 01/01/2017, but using ALL('Sales Detail') removes that filter.

Try: 

Sales Order Total Value Containing Items = CALCULATE(SUM('Sales Detail'[Sales]),ALLSELECTED('Sales Detail'),VALUES('Sales Detail'[Sales Order #]))

[USABB_Data]

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.