Combine multiple measures in a table

[vipett]

I have a dilema, today I have a matrix with

Products on Rows

YYYYMM in Columns

Forecast as Values 

Based on what "Version" I select, the date range in YYYYMM changes.

Next to this, I have another matrix with measures like "Avg Forecast 6M", "Avg Shipped 6M LY", "Avg Open Orders 6M"

Now I have too many products which means I need to scroll down in the tables and that means I need to scroll in two tables.

Is there a way to combine these two tables, basically showing: 202601 | 202602 | … | 202612 | Avg Forecast 6M | Avg Shipped 6M LY | Avg Open Orders 6M and for the YYYYMM columns use the Forecast measure, and then the other measures for the other columns?

I could do this with a calculated table, but due to the data size, it becomes very very slow..

[burakkaragoz]

Hi @vipett ,

@ajaybabuinturi  has provided the correct architectural approach (The "Hybrid" Disconnected Table).

I want to address your concern about Performance and fix a potential Sorting Issue you will face with that code.

1. Performance Reassurance You mentioned: "I could do this with a calculated table, but due to the data size, it becomes very slow." Don't worry! The table suggested here is a Dimension Table, not a Fact Table. It will only contain one row per month + 3 rows for your measures (e.g., approx 50 rows total). It will satisfy the query instantly and won't slow down your report like a calculated fact table would.

2. The "Sorting" Trap (And how to fix it) If you just UNION the names, Power BI will sort them Alphabetically. Your "Avg Forecast" might appear before "202601". You need a Sort Order column to force the measures to the end.

Here is the refined DAX to handle the sort order automatically:

Unified Columns = 
UNION(
    -- Part 1: The Dates
    -- We use the YYYYMM as the Sort Order so dates sort correctly
    ADDCOLUMNS(
        DISTINCT( SELECTCOLUMNS('Calendar', "ColumnLabel", CONVERT('Calendar'[YYYYMM], STRING)) ),
        "SortOrder", VALUE([ColumnLabel]), 
        "Type", "Date"
    ),
    
    -- Part 2: The Measures
    -- We use a very high number (999901+) for Sort Order to force these to the FAR RIGHT
    DATATABLE(
        "ColumnLabel", STRING, 
        "SortOrder", INTEGER, 
        "Type", STRING,
        {
            {"Avg Forecast 6M",     999901, "Measure"},
            {"Avg Shipped 6M LY",   999902, "Measure"},
            {"Avg Open Orders 6M",  999903, "Measure"}
        }
    )
)

Final Step: Once you create this table, select the ColumnLabel column in the Data View, click "Sort by Column" in the ribbon, and select SortOrder.

Now your Matrix will look exactly right: 202601 | ... | 202612 | Avg Forecast 6M | ...

This gives you the best of both worlds: Speed and the correct Layout.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[ajaybabuinturi · Accepted solution]

Hi @vipett,

Could you please try with below steps.

1. Create a disconnected table for your final columns

UnifiedColumns(Disconnected table) =
UNION(
    SELECTCOLUMNS(
        DISTINCT('Calendar'[YYYYMM]),
        "ColumnType", "Period",
        "ColumnKey", 'Calendar'[YYYYMM]
    ),
    DATATABLE(
        "ColumnType", STRING,
        "ColumnKey", STRING,
        {
            {"Measure", "AvgForecast6M"},
            {"Measure", "AvgShipped6MLY"},
            {"Measure", "AvgOpenOrders6M"}
        }
    )
)

2.Create a measure that returns the correct value based on row context, this will be main measure used in the matrix

Unified Value :=
VAR ColType = SELECTEDVALUE(UnifiedColumns[ColumnType])
VAR Key = SELECTEDVALUE(UnifiedColumns[ColumnKey])
RETURN
SWITCH(
    TRUE(),
    ColType = "Period",
        CALCULATE(
            [Forecast],
            'Calendar'[YYYYMM] = Key
        ),
    Key = "AvgForecast6M", [Avg Forecast 6M],
    Key = "AvgShipped6MLY", [Avg Shipped 6M LY],
    Key = "AvgOpenOrders6M", [Avg Open Orders 6M],

    BLANK()
)

3.Build your matrix as below

Rows: Products
Columns: UnifiedColumns[ColumnKey]
Values: Unified Value measure

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[vipett]

Thanks, I had to switch 'Key' to something else because Key was a reserved word, but otherwise excellent!