Help with a formula

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 ?

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:

5 Likes

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

2 Likes

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.

2 Likes