Customized Matrix Visual

[Pfoster]

Hello again,

I was searching the web and also trying to build a solution (also with help of ChatGPT, but no success):
I need to build a kind of Matrix Visual, where I have the Actual Net Sales per month of the selected year. Then, in an additional column, I have, for comparison reasons, the budget YTD per month. Then two kinds of deviations: One vs. Budget, the other vs. Prior Year. And last but not least, the Deviation in terms of volumes vs. Budget and Prior Year. 

The single measures are all available and calculating correctly. But I am not able to build them in one visual. Is there any solution for this kind of Matrix? 

Thank you for any help, tip and trick! 

[v-dineshya · Accepted solution]

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

[apicados]

Hola! Ya intentó usar el complemento Zebra?

[v-dineshya · Accepted solution]

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

[Pfoster · Accepted solution]

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! 

[v-dineshya]

Hi @Pfoster ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Pfoster]

Thank you. I was afraid that would be the answer... hmm, perhaps, I will find a workaround to get to a similar result

[vojtechsima]

Hey, @Pfoster ,
Matrix work in way that for every measure you put to values, each value in a "Columns" column with recalculates this measure as well. So if you put year month into "Columns", you'll get all measure for each yearmonth.

If you want the same behaviour as in your picture, where each calculation is sometimes present in a particular column, and sometimes not, you'll have to construct custom measures to mimic this and drop 'Columns'. There's probably no built-in easy drag-and-drop configurator for this.