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

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)))
USABB_Data's avatar
USABB_Data
Icon for Helper II rankHelper II
7 years ago

Thanks that does work but I want to avoid hardcoding dates because I want the measure to be reausable.  I have the date filter already on my report as well.

  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    So then replace the hardcoded value with a measure or expression that returns the date from your date filter. 

     

    Measure = 
VAR _orders=VALUES(Orders[OrderId]) 
VAR _curDate = SELECTEDVALUE(Orders[Date])
RETURN
CALCULATE(SUM(Orders[Sales]),FILTER(ALL(Orders),Orders[OrderId] in _orders && Orders[Date]>=_curDate))
  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, USABB_Data 

    If possible, could you share your simple sample pbix file for us have a test?

     

    Best Regards,

    Lin

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

    I don't have Dropbox but hopefully the screenshots will provide more clarity.

     

    Used your demo and created an Order Date table with a duplicate OrderID of 2 to mimmick our setup (1st screenshot).  Then I filter the Order Date to just 7/11/2019 (2nd screenshot).  I want the measure to now show just 75 because that's the total for the measure on 7/11/2019 and the 5/15/2019 data is irrelevant.