Help with a formula

B18ANU
Participant II

Hi,

I have the following situation and been trying really hard to find a solution for the past couple of days to no avail. Right now i have two tables Delivery and Delivery_Details, the latter being part_of the former.
This is a simplification of the tables :
Delivery = Delivery id, Est. Total( This should be an app formula to calculate cost based on Est. Cost )
Delivery_Details = Delivery Details id, Delivery id, Location, Est. Cost ( Formula based on price per mile )

Assume the following example :

Delivery table
ID1, 140

Delivery_Details
IDD1, ID1, 28 Lane, 20
IDD2, ID1, 28 Lane, 20
IDD3, ID1, 28 Lane, 20
IDD4, ID1, 56 Road, 30
IDD5, ID1, 86 Road, 50

In this example the Est. Total, shows 140 but i would like it to be 100 based on the fact that the first 3 rows are all going to the same address so the cost should only be taken into account once.

Is there any way i could do this with just a sum formula ? If not any workarounds i might be able to do ?

Solved Solved
0 3 193
1 ACCEPTED SOLUTION

Steve
Participant V

Try this:

SUM(
  SELECT(
    Delivery_Details[Est. Cost],
    AND(
      ([Delivery id] = [_THISROW].[Delivery id]),
      ISBLANK(
        FILTER(
          "Delivery_Details",
          AND(
            ([Delivery id] = [_THISROW-1].[Delivery id]),
            ([Location] = [_THISROW-1].[Location]),
            ([_ROWNUMBER] < [_THISROW-1].[_ROWNUMBER])
          )
        )
      )
    )
  )
)

See also:

View solution in original post

3 REPLIES 3

Steve
Participant V

Try this:

SUM(
  SELECT(
    Delivery_Details[Est. Cost],
    AND(
      ([Delivery id] = [_THISROW].[Delivery id]),
      ISBLANK(
        FILTER(
          "Delivery_Details",
          AND(
            ([Delivery id] = [_THISROW-1].[Delivery id]),
            ([Location] = [_THISROW-1].[Location]),
            ([_ROWNUMBER] < [_THISROW-1].[_ROWNUMBER])
          )
        )
      )
    )
  )
)

See also:

B18ANU
Participant II

Hi Steve,

The formula provided is hurting my brain a bit but has worked perfectly. I dont understand the logic behind

ISBLANK(
FILTER(
โ€œDelivery_Detailsโ€,
AND(
([Delivery id] = [_THISROW-1].[Delivery id]),
([Location] = [_THISROW-1].[Location]),
([_ROWNUMBER] < [_THISROW-1].[_ROWNUMBER])

,but i will have a look at the link provided and try to understand it as this will definitely be useful later on. Thank you very much for taking the time and helping me out, been pulling my hairs out as i had no ideea what to search for.

Kind regards,
Stu

The FILTER() expression finds all Delivery_Details rows that have the same Delivery id and Location column values as the Delivery_Detail row being examined by SELECT(), but has a _ROWNUMBER column value less than the row being examined.

In essence: give me all rows with for the same delivery at the same location but with a lower row number.

If there arenโ€™t any such rows (ISBLANK(...), it means the row weโ€™re looking at is the one with the lowest row number for this particular delivery ID and location combination.

There will only ever be one row with the lowest row number for a particular delivery ID and location combination, so if we get the estimated cost from only those rows, we know weโ€™ll only ever get one estimated cost per delivery ID, location combination.

We then sum those one-per estimated costs.

Top Labels in this Space