Forum Discussion
Helper IICalculate 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#M143881
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
- Cmcmahan
Resident Rockstar
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_DataThanks 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.
v-lili6-msftCommunity Support
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')
)DOCSINNER 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]) )
