Forum Discussion
Helper IICalculate Order Value Total ($) When Containing Item used in Filter - Duplicate Order #'s in system
Community Supporthi, 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
Helper IIThis 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).
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
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
Resident Rockstar
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
