I have three tables with the below columns.
I am trying to filter and add all the purchases for the day that are allocated to food cost in the Ref Stock Items Table.
I have the following formula
SUM(SELECT(Line Items[Cost],AND([Banking Date]=Today(),[Item].[ITEM].[Type]=“Food Cost”)))
But I get the following error
Column ITEM in expression ‘[Item].[ITEM].[Type]’ does not contain a reference.
Can someone help me with the correct dereference?
Tables and their Row
_RowNumber Number
Timestamp DateTime
Banking ID Text
Credit Card ID Text
Banking Date Date
TOTAL TURNOVER Price
Less credit cards Price
Deliveries Price
Sub Total Price
Less cash food cost Price
Banking
_RowNumber Number
Timestamp DateTime
Invoice No Text
Date Date
Supplier Ref
Item Ref
Quantity Number
Price per Unit Price
Cost Price
ITEM ID Text
ITEM Text
Supplier Text
Туре Text
Price Text
Related Line Items List
Solved! Go to Solution.
You can only dereference the value of a column of type Ref. The only column of type Ref I see in your app is the Item column of the Line Items table. It appears that column refers to the Ref Stock Items table, which has an ITEM column that is not of type Ref. In order to dereference [Item].[ITEM]
, the ITEM column of the Ref Stock Items table must be of type Ref. That said, I don’t think you have a need to dereference TEXT. Try this:
SUM(
SELECT(
Line Items[Cost],
AND(
(TODAY() = [_THISROW].[Banking Date]),
(“Food Cost” = [Item].[Type])
)
)
)
You can only dereference the value of a column of type Ref. The only column of type Ref I see in your app is the Item column of the Line Items table. It appears that column refers to the Ref Stock Items table, which has an ITEM column that is not of type Ref. In order to dereference [Item].[ITEM]
, the ITEM column of the Ref Stock Items table must be of type Ref. That said, I don’t think you have a need to dereference TEXT. Try this:
SUM(
SELECT(
Line Items[Cost],
AND(
(TODAY() = [_THISROW].[Banking Date]),
(“Food Cost” = [Item].[Type])
)
)
)
Thanks Steve. I think that worked.
Really appreciate the assistance. Hopefully I’ll get the hang of this sometime soon.
User | Count |
---|---|
43 | |
29 | |
23 | |
21 | |
13 |