cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
USABB_Data Frequent Visitor
Frequent Visitor

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

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

13 REPLIES 13
Super User
Super User

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

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 Frequent Visitor
Frequent Visitor

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

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.

Community Support Team
Community Support Team

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

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

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
USABB_Data Frequent Visitor
Frequent Visitor

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

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.

 

Demo Screenshot.PNG

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
Super User
Super User

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

Try this measure:

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

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

Parameter issue on cmc's latest suggestion:

 

Parameter Not Correct.PNG

Community Support Team
Community Support Team

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

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
USABB_Data Frequent Visitor
Frequent Visitor

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

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.


  

Super User
Super User

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,435)