Compare aggregation with fixed number
The data model looks like this:
I have an insight that sums the per-order-line gross profit (not shown, but comes from the Order_Line table), grouped by OrderDate (from the Order table) and FAPA_Code (from the MarketingCodes table).
I would like to compare the result of this aggregation within an insight to the Gross_Profit value in the FAPA_Budget table, for the given date and FAPA code.
I have tried using the lookup function but was unsuccessful.
I have tried joining FAPA_Budget to MarketingCodes on the FAPA field and FAPA_Budget to Order on OrderDate, but this creates a circular reference.
Any ideas on how to compare an aggregation to a fixed number within an insight?