Forum Discussion
Post PartisanAdding another dimension to matrix table with values as row headers
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?
6 Replies
- DataInsights
Super User
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.
DeBIeHey 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
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.
- gvlado
Advocate III
You can try with switch value in formating menu (Format Visual)
