Adding another dimension to matrix table with values as row headers

[DeBIe]

Hey all,

The management requested an overview of all important metrics. I've found a way to use values as row headers, because this is the way they want to see it. I also would like to add the dimensions department and administration to the left side of the matrix, so that they can drill down. But I don't think that is possible when using the values as row headers. Any thoughts how to achieve this?

[gvlado]

You can try with switch value in formating menu (Format Visual)

[DataInsights]

@DeBIe,

You can achieve this with the disconnected table/SWITCH measure technique.

1. Create a disconnected table (no relationships) that contains a column with each measure name, and a column for sorting.

2. Create a measure like this:

Switch Measure =
SWITCH (
    SELECTEDVALUE ( DimMeasure[Measure Name] ),
    "Total Autobooked Invoices %", [Total Autobooked Invoices %],
    "Total Active Administrations", [Total Active Administrations]
)

3. In the matrix rows field well, add DimMeasure[Measure Name], Department, and Administration.

4. In the matrix values field well, add [Switch Measure].

If the formatting isn't what you need, you can use the FORMAT function but that converts numbers to text. A more robust solution is calculation groups which offer custom formatting for each calculation item. Let me know if this solution meets your requirements, and if not we can explore these other options.

[DeBIe]

Hey @DataInsights ,

I'm about to try your solution, but I have a few question. 

1. I created a new table with these measures as my column names (I filled in 3 for testing). I don't know what you mean by adding a sorting column? 

2. SELECTEDVALUE ( DimMeasure[Measure Name] ) What do I need to fill in here? The table where my measures are in?

Thanks for your time!

[DataInsights]

@DeBIe,

1. A sorting column enables you to display the measures in the desired order. In your example, "Total Autobooked Invoices %" is first, so this row would have a 1 in the sort column. In the data view, you can sort DimMeasure[Measure Name] by DimMeasure[Sort Order].

2. Yes, this is the table[column] that contains your measure names.

[DeBIe]

Hey @DataInsights 

Thank you very much. It's almost exactly what I need!

The formatting is now indeed the remaining thing that needs to be fixed. Some measures are in % or in #, and the specific number of decimals. Can you help me further on this with calculation groups to make specific formatting possible?

Appreciated!!

[DataInsights]

@DeBIe,

Excellent! Here are some articles on calculation groups. You'll need to install Tabular Editor (free tool) to create calculation groups.

https://www.sqlbi.com/articles/introducing-calculation-groups/ 

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/