Forum Discussion
Pulling a Measure into a Table based on Filters
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.
1 Reply
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=czNHt7UXIe8not use what you want achieve using match by
