How to create Matrix Headers?

[ERing]

I'm stuck and still looking for help on how to create Matrix Headers like in the visual below. I've been suggested using parameters, calculation groups, and a disconnected table with switch measure, but I haven't been able to make any of those suggestions work with my data.

Would be extremely appreciative if anyone is willing to use the sample file to provide a solution.

Matrix Headers Example 

[danextian · Accepted solution]

Hi @ERing 

Aside from what @ERing  mentioned, you can create a headers table using DAX or M (via the enter data option). The key point is that a physical column is required to assign the output of a measure. Field parameters are suitable if your goal is to choose which columns to display, but they are not compatible with hierarchical structures. Please see the attached pbix.

[ERing]

@danextian @OwenAuger 

I've run into an unexpected issue after implementing the solution.

Measures that result in "0" are not being popluated in my matrix. This occurs when the Matrix is filtered to only show limited CHANNEL_NAME where there may not be values for each column (Actual, PY, Budget, % vs PY, % vs Budget)

Each category (Spend, Revenue, Calls, Conversions) should have a column for Actual, PY, Budget, % vs PY,  % vs Budget). 

You can see in the screen capture that the "Spend" category is missing Actual.
The "Revenue" category is missing Actual, PY, % vs PY.
The "Calls" category is missing Actual, PY, % vs PY.

The "Budget" and "% vs Budget" are showing becuase I've set those measures = " ".

Is there anything I can do to correct this to show each column shows even if there is no value?

[OwenAuger · Accepted solution]

Hi again @ERing ,

Good point! Columns with all blanks are hidden by default.

I would recommend selecting "Show items with no data" on the Column fields only (assuming that is where the measures are placed).

This will ensure that all measure columns are always displayed regardless, without introducing unwanted nonblank measure values.

Does this work for you?

[ERing]

Wonderful! I'm glad it was an easy fix.

[ERing]

@danextian @OwenAuger 

Thank you both very much for your help! I was finally able to get the solution applied to my real data.

[danextian · Accepted solution]

Hi @ERing 

Aside from what @ERing  mentioned, you can create a headers table using DAX or M (via the enter data option). The key point is that a physical column is required to assign the output of a measure. Field parameters are suitable if your goal is to choose which columns to display, but they are not compatible with hierarchical structures. Please see the attached pbix.

[OwenAuger]

Hi @ERing 

Two main options I would consider (updated PBIX attached):

1. Calculation group

• Create one calculation item per measure, with name matching the measure and the expression simply referencing the measure.
• Set the format string expression the same as the measure (enclosed in double quotes).
• Add a "Measure Group" calculated column to the calculation group.
• Add a "Measure Group Ordinal" column to specify the sort order of Measure Group (note that its expression cannot reference the "Measure Group" column, but can reference the "Measure" calculation item column.
• Set "Measure Group" to sort by "Measure Group Ordinal".
• Place "Measure Group" and "Measure" on Columns of the matrix, and an arbitrary measure on Values (as it will be replaced by each calculation item expression).
• Fully expand matrix Columns.
• Format matrix and rename fields as required. I replaced "Measure Group" with a space character.

2. SWITCH measure

• Create a regular table in the same structure as the above calcuation group.
• Create a measure as follows:

Selected Measure = 
SWITCH (
    SELECTEDVALUE ( 'Measure Table'[Measure] ),
    "Leads_Budget", [Leads_Budget],
    "Leads_Actual", [Leads_Actual],
    "Conversions_Budget", [Conversions_Budget],
    "Conversions_Actual", [Conversions_Actual],
    "Marketing_Budget", [Marketing_Budget],
    "Marketing_Spend_Actual", [Marketing_Spend_Actual],
    "Revenue_Budget", [Revenue_Budget],
    "Revenue_Actual", [Revenue_Actual]
)

• Specify a dynamic format string for this measure:

VAR NumberFormat = "#,0"
VAR CurrencyFormat = "\$#,0;(\$#,0);\$#,0"
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Measure Table'[Measure] ),
        "Leads_Budget", NumberFormat,
        "Leads_Actual", NumberFormat,
        "Conversions_Buget", NumberFormat,
        "Conversions_Actual", NumberFormat,
        "Marketing_Budget", CurrencyFormat,
        "Marketing_Spend_Actual", CurrencyFormat,
        "Revenue_Budget", CurrencyFormat,
        "Revenue_Actual", CurrencyFormat
    )

• Set "Measure" to sort by "Ordinal" and "Measure Group" to sort by "Measure Group Ordinal".
• Place "Measure Group" and "Measure" on Columns of the matrix, and "Selected Measure" on Values.
• Fully expand matrix Columns.
• Disable Column subtotals.
• Format matrix and rename fields as required. I replaced "Measure Group" with a space character.

Field parameters aren't a good option for this kind of layout because the set of measures from the field parameter that are displayed in the visual is determined once at the visual-level, so different sets of measures cannot be displayed within different groups.

Hopefully that helps!