Forum Discussion

DeBIe's avatar
DeBIe
Post Partisan
4 years ago

Adding 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 di...
Need Help
DataInsights's avatar
DataInsights
Super User
4 years ago

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's avatar
DeBIe
Post Partisan
4 years ago

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's avatar
    DataInsights
    Super User
    4 years ago

    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.