Summing a column from multiple users in the same fleet

[Jack_Bates]

In the annualloadbook app we sum the columns in expenses for [Gallons] of fuel purchased. The expression below works great for each individual app user. The Solo IFTA report pulls the totals from the default app user and assigned Unit

I need to modify the expression below to be able to SUM the [Gallons] in table expenses Summing units in the same [Fleet],  Fleets has its own Table and is Ref in the Truck and App User Tables

SUM(SELECT([Expenses][Gallons], [State] = "ALBERTA")) 

So the app would have to SUM [Gallons],[State] = “ALBERTA” from each app user’s [Expenses] in the same Fleet ID

SUM(SELECT([Expenses][Fuel Tax paid], [State] = "ALBERTA")) Same expression for fuel tax paid for each app user’s in the same Fleet ID

Where expression needs to beApp User Fleet Ref column

[TeeSee1 · Accepted solution]

Though I cannot be 100% certain of your app architecture, I am assuming the following

The IFTA Report has a USER (ref to App User) and [EXPENSES] is a list of refs to the Expenses table that have the row's USER. Also your EXPENSES table has no direct FLEET column but it is indirectly retrievable from the USER.

Then try

SUM(
 SELECT(
  EXPENSES[GALLONS], 
  AND(
   [USER].[FLEET] = [_THISROW].[USER].[FLEET],
   [State] = "ALBERTA"
  )
 )
)

[Jack_Bates]

SUM(SELECT([Expenses][Gallons],
AND([Truck].[Fleet]=[_Thisrow].[Fleet],[State]= "ALBERTA")))

This is working 

Thank you I was stuck