Forum Discussion

clarkey1988's avatar
clarkey1988
Helper II
5 years ago
Solved

Sumifs Between two tables

Hi,

 

I'm wondering if anyone can help. I have the below in calculated column. I'm trying to calculate the ('Trip Detail'[Miles]) from Trip detail table based off the route number in the row of TARDBQuery. This is pulling correctly. However, I now need to add two additional filters using month and year for both of the same tables.

 

Current:

Master Miles = CALCULATE(SUM('Trip Detail'[Miles]),FILTER('Trip Detail','Trip Detail'[RouteNumber]=CALCULATE(VALUES(TARDbQuery[Route]))))

 

Need to add to current:

 

FILTER('Trip Detail','Trip Detail'[Month]=CALCULATE(VALUES(TARDbQuery[Month]))))

FILTER('Trip Detail','Trip Detail'[Year]=CALCULATE(VALUES(TARDbQuery[Year]))))

 

Please could anyone assist.
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    clarkey1988 , I am assuming you are creating a new column

    new column in TARDbQuery

     

    sumx(filter('Trip Detail', 'Trip Detail'[RouteNumber]=TARDbQuery[Route] && 'Trip Detail'[Month]=TARDbQuery[Month] && 'Trip Detail'[Year]=TARDbQuery[Year]),'Trip Detail'[Miles])

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    clarkey1988 , I am assuming you are creating a new column

    new column in TARDbQuery

     

    sumx(filter('Trip Detail', 'Trip Detail'[RouteNumber]=TARDbQuery[Route] && 'Trip Detail'[Month]=TARDbQuery[Month] && 'Trip Detail'[Year]=TARDbQuery[Year]),'Trip Detail'[Miles])

    • clarkey1988's avatar
      clarkey1988
      Helper II
      5 years ago

      Thanks! That worked. However, I have one additional thing I need to figure out. I now need to divide these miles using the distinct count of order numbers in trip detail using the same filters.

      I.E:

      In January 2020 route number SKAC-01:

      6,190 miles/22 order numbers = 281 miles per order number

       

      I put the below in but I get an error

       

      (DISTINCTCOUNT(FILTER('Trip Detail','Trip Detail'[RouteNumber]=TARDbQuery[Route]&&'Trip Detail'[Month]=TARDbQuery[Month]&&'Trip Detail'[Year]=TARDbQuery[Year]),'Trip Detail'[Ordernumbers])