I'm trying to create a measure in Excel Power Pivot that will return the Total value for filtered rows in based on whether the column matches a value in another table. The formula I have is below.
=SUMX('Table1',LOOKUPVALUE([$ Allocation],Table1[Cost Centre Owing],MAX('Table2'[Cost Centre])))
This measure is syntactically correct however when I add the measure to my pivot table I get the message:
The query did not run or the data model could not be accessed. MdxScript(Model) (6, 57) Calculation error in a measure 'Table1'[Owed] A table of multiple values was supplied where a single value was expected
Can you please explain why this is occurring as I've wrapped the MAX function around the search value to make sure it's a single value. Thank you!