Customized Matrix Visual

Hi Pfoster ,

Thank you for reaching out to the Microsoft Community Forum.

Hi vojtechsima , Thank you for your prompt response.

Hi Pfoster , Power BI Matrix supports row and column hierarchies but does not allow arbitrary column groups like “SALES” and “VOLUME” with mixed measures.

Please try below alternative workarounds..

1. Create a disconnected table for column headers like “Jan”, “Feb”, “YTD vs Budget Abs”, “YTD vs Budget %”. Use SWITCH() in a measure to return the correct value based on the selected column. Bind this table as columns in a Matrix visual.

2. Create Field Parameters for measures like Actual, Budget, Variance, Volume Variance. Combine with a Month hierarchy.

3. Use a custom visual like Inforiver, Zebra BI and Acterys Matrix. These support multi-level headers, variance columns, and advanced formatting.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

Hello v-dineshya,
thank you for your answer. Today, I have had time to work further on this topic, and you advise with the disconnected table brought the solution:

here is the code I used for the table:

Display = 
DATATABLE(
    "Label", STRING, "Sort", INTEGER, "Kind", STRING, "MonthNo", INTEGER,
    {
        {"Jan",  1, "Month", 1},
        {"Feb",  2, "Month", 2},
        {"Mrc",  3, "Month", 3},
        {"Apr",  4, "Month", 4},
        {"May",  5, "Month", 5},
        {"Jun",  6, "Month", 6},
        {"Jul",  7, "Month", 7},
        {"Aug",  8, "Month", 8},
        {"Sep",  9, "Month", 9},
        {"Oct", 10, "Month",10},
        {"Nov", 11, "Month",11},
        {"Dec", 12, "Month",12},
        {"Budget",            100, "KPI", BLANK()},
        {"YTD vs BUD [abs.]",        101, "KPI", BLANK()},
        {"YTD vs BUD [%]",        102, "KPI", BLANK()},
        {"YTD vs PY [abs.]",         103, "KPI", BLANK()},
        {"YTD vs PY [%]",         104, "KPI", BLANK()},
        {"YTD volume vs Budget [%]",105, "KPI", BLANK()},
        {"YTD volume vs PY [%]",106, "KPI", BLANK()}
    }
)

Now, I have the month, one column for the budget and my deviations. 

To fill the columns with life, I used this measure:

Display Value = 
VAR _kind  = SELECTEDVALUE ( Display[Kind] )
VAR _label = SELECTEDVALUE ( Display[Label] )
VAR _mno   = SELECTEDVALUE ( Display[MonthNo] )
VAR _scale = 1000000.0

VAR _year =
    COALESCE (
        SELECTEDVALUE ( GemeinsameJahre[Year] ),
        CALCULATE ( MAX ( 'Sales Data'[Year] ), REMOVEFILTERS ( Display ) )
    )
VAR _lastM =
    CALCULATE (
        MAX ( 'Sales Data'[Posting period] ),
        KEEPFILTERS ( 'Sales Data'[Year] = _year ),
        KEEPFILTERS ( 'Sales Data'[Scenario] = "Actual" ),
        REMOVEFILTERS ( Display )
    )

VAR _ytdFilter =
    FILTER (
        ALL ( 'Sales Data'[Year], 'Sales Data'[Posting period] ),
        'Sales Data'[Year] = _year &&
        'Sales Data'[Posting period] <= _lastM
    )

VAR _pyYtdFilter =
    FILTER (
        ALL ( 'Sales Data'[Year], 'Sales Data'[Posting period] ),
        'Sales Data'[Year] = _year - 1 &&
        'Sales Data'[Posting period] <= _lastM
    )
VAR _fullYearBudget =
    CALCULATE (
        [BudgetNS_in_RC],
        ALL('Sales Data'[Year],'Sales Data'[Posting period]),
        'Sales Data'[Year] = _year  
    )
VAR _hasMonthlyBudget =
    NOT ISBLANK (
        CALCULATE (
            [BudgetNS_in_RC],
            KEEPFILTERS ( 'Sales Data'[Year] = _year ),
            KEEPFILTERS ( 'Sales Data'[Posting period] = _lastM )
        )
    )
VAR _ytdBudAdj =
    IF (
        _hasMonthlyBudget,
        CALCULATE ( [BudgetNS_in_RC], _ytdFilter ),
        _fullYearBudget * DIVIDE ( _lastM, 12 )
    )
VAR _ytdAct   = CALCULATE ( [ACT_NS_in_RC],     _ytdFilter )
VAR _ytdAbs = _ytdAct - _ytdBudAdj
VAR _ytdBud   = CALCULATE ( [BudgetNS_in_RC])
VAR _pyYtdAct =     CALCULATE( [PY_NS_in_RC],  _pyYtdFilter)
VAR _ytdPct = DIVIDE ( _ytdAbs, _ytdBudAdj )

VAR _ytdVolAct = CALCULATE ( [ActMT],       _ytdFilter )
VAR _ytdVolBud = CALCULATE ( [BudgetMT],    _ytdFilter )
VAR _pyYtdVol  = CALCULATE ( [PY_MT],       _pyYtdFilter )
VAR _volPctBud = DIVIDE ( _ytdVolAct - _ytdVolBud, _ytdVolBud )
VAR _volPctPY  = DIVIDE ( _ytdVolAct - _pyYtdVol,  _pyYtdVol  )

RETURN
SWITCH (
    TRUE (),

    _kind = "Month",
        CALCULATE ( [ACT_NS_in_RC],
            KEEPFILTERS ( 'Sales Data'[Year] = _year ),
            KEEPFILTERS ( 'Sales Data'[Posting period] = _mno )
        ) / _scale,

    _label = "Budget",
        _ytdBud / 12 / _scale,

    _label = "YTD vs BUD [abs.]", 
        _ytdAbs / _scale,

    _label = "YTD vs BUD [%]",    
        _ytdPct*100,

    _label = "YTD vs PY [abs.]",
        ( _ytdAct - _pyYtdAct ) / _scale,

    _label = "YTD vs PY [%]",
        DIVIDE ( _ytdAct - _pyYtdAct, _pyYtdAct )*100,

    _label = "YTD volume vs Budget [%]",
        _volPctBud*100,

    _label = "YTD volume vs PY [%]",
        _volPctPY*100,

    BLANK ()
)

 Now, I will use some time to polish the table and try to bring the %-columns into %-format (at least, that %-sign is shown up). Perhaps, I also find a solution, to bring up some table boarders for the budget column, so it is better to read. But only in terms of topic, the table is finished and your hint with the disconnected table is the solution! Thank you!