When dragging the above value into a matrix visual it shows the below matrix:
The A_Depot and B_Depot columns are from the Category column in the source table. I've applied a filter using DAX to restrict MTDValue as A_Depot, and restrict MTDTarget as B_Depot.
All is fine so far. I have some measures, created in DAX, that use a lookup table to provide some context filtering. The lookup table is:
When using the above Row Headers column (although in this end result these are row columns headers - just drag the 'Row Headers' field from Rows to Columns) and the ACategory or BCategory columns as context filtering - I get the below result:
This looks fine too, however I would like to merge the immediately above table onto the end of the first table so to produce the below result:
To get the above image I had the first table overlap the second table.
This won't do as the final solution as the number of Depot (i.e. Birmingham, Dudley, etc..) will likely be in the hundreds so when the user scrolls through the matrix visual I need both the table data and measures data to scroll as one.
How can I get the measures to merge onto the end of the data table horizontally?
I would preferably like to do this using DAX over doing it in Power Query as doing it in the latter could well screw-up the status-quo. What is the best practice to achieve what I want and how? Should I be looking to use 'AddColumns' or something similar?