I have two matrices built in the report view that I would like to sync by row order. There is one common column in the matrices labeled "Trace Number", and the remaining columns are calculated measures.
My source table looks like the following, where there can be any number of scenarios, there are always 40 traces, the date spans ~1year, there are currently 57 slots (but this could be added to).
| Scenario | Trace Number | Date | Slot | Value |
| ScenarioA | 1 | 1/1/2022 | SlotX | 10 |
| ScenarioA | 2 | 1/1/2022 | SlotX | 12 |
| ScenarioA | 1 | 1/2/2022 | SlotX | 11 |
| ScenarioA | 2 | 1/2/2022 | SlotX | 13 |
| etc | Etc | Etc | Etc | Etc |
I have 4 measures, displayed in two matrices. The tables are currently constructed as follows:
Table A:
| Trace Number | MeasureA | MeasureB | MeasureC |
| 1 | 10 | 12 | 12 |
| 2 | 11 | 9 | 23 |
| etc | etc | etc | etc |
Table B:
| Trace Number | MeasureD |
| 1 | 14 |
| 2 | 54 |
| etc | etc |
Each table is constructed with measures calculated against calculated tables that filters data from the source table, based on the Scenario. Additionally, both tables use the trace number as row labels from the same dimension table of unique trace numbers. The dimension table has a 1 to many relationship to the source table and both calculated tables which define subsets of data relevant for TableA and TableB.
I want both tables to sort by the trace number - so, if I select the header for measureA so that the table sorts in ascending order, whatever the trace number order is in TableA, I want it to be the same in TableB. Conversely, if I select the header for MeasureD to sort in ascending order, I want the resulting trace number order in TableB to define the row order in TableA. How can I achieve this?
Thank you
