Pulling a Measure into a Table based on Filters

[dom-mitchell]

Hi there, this one has me stumped...

What I am looking to achieve is a calculated column ( [Invoiced] ) in a table ( 'MonthNumber' ) that shows the value from a measure ( 'Invoiced Deal Data'[Total Invoiced Business in GBP] ) based on the data in columns [Consultant] and [Month].

'MonthNumber' has data in it that looks like this:

Consultant	Month Number	Month	Invoiced
A	1	April 2023
A	2	May 2023
A	3	June 2023
A	4	July 2023
B	1	May 2023
B	2	June 2023
B	3	July 2023
C	1	July 2023

The measure I'm looking to use ( [Total Invoiced Business in GBP] ) is the aggreated sum of a series of different calculated columns in various other tables but all the date columns across the whole dataset are all linked via relationships to common Calendar table - 'FT Dates'.

Likewise, the Consultant column has a relationship to another table 'ConsultantList' which is the common list and also has a relationship to the Consultant column in 'Invoiced Deal Data'. 

I've tried a number of different ways but everthing ends up in an #ERROR in the new Invoiced column. Most recent attempt is 

Invoiced = CALCULATE('Invoiced Deal Data'[Total Invoiced Business in GBP], MATCHBY (ConsultantList[Consultant],MonthNumber[Consultant],'Invoiced Deal Data'[Invoiced Date]),MonthNumber[Month])

Absolute car crash I'm sure but if anyone can point me in the right direction, that would be amazing. 

[amitchandak]

@dom-mitchell , Columns are static, so using measure or expectation is that they will filter based on selection will not hold.

If there is a join, the measure do not need that relationship in filter again

to create a column in the table using another table

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

not use what you want achieve using match by