Forum Discussion

USABB_Data's avatar
USABB_Data
Icon for Helper II rankHelper II
7 years ago

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#M143881   However, we have duplicate order #'s in our system.....
Need Help
USABB_Data's avatar
USABB_Data
Icon for Helper II rankHelper II
7 years ago

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
Cmcmahan's avatar
Cmcmahan
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Try this measure:

OrderTotal = 
CALCULATE( SUM(Orders[Sales]),ALLEXCEPT(ALLSELECTED(Orders), Orders[OrderId]) )
  • USABB_Data's avatar
    USABB_Data
    Icon for Helper II rankHelper II
    7 years ago

    Parameter issue on cmc's latest suggestion:

     

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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

       

      • USABB_Data's avatar
        USABB_Data
        Icon for Helper II rankHelper II
        7 years ago

        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.