cancel
Showing results for
Did you mean:
Highlighted
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

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

## 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.

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

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

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

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

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

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

Announcements

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

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

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)