Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DeBIe
Post Partisan
Post Partisan

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 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?

 

DeBIe_0-1652863765528.png

 

6 REPLIES 6
gvlado
Advocate II
Advocate II

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

gvlado_0-1676979439580.png

 

DataInsights
Super User
Super User

@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.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

 

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!!

@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/ 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.