cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)