Power BI is a powerful business intelligence tool that allows you to connect, transform and visualize data from various sources. One of the most common visualizations in Power BI is the matrix, which provides a tabular representation of data with rows, columns and values.
When you use measures in a matrix, they are displayed as values in the columns by default. However, this can sometimes be cluttered or inappropriate depending on what type of data you want to display. For example, if you want to create a contribution margin calculation that shows different items below each other at row level.
In this blog post, I will show you how to display the measures in a matrix in Power BI at row level by using a simple option in the formatting area. This is useful if you want to compare multiple measures for a single category or if you want to create a hierarchical structure of measures.
Step 1: Create a matrix with the desired fields
First, you need to create a matrix with the fields you want to display. In my example, I'm using the retail analytics sample data from Microsoft Dynamics 365. I want to display the sales values by item category using the following measures:
- Average unit price
- Sales in the last year
- Sales this year
- Value
- Destination
- Status
I drag the Element > Category field to the row axis and the Measures to the value axis. Power BI automatically creates a matrix like this:
Step 2: Activate the "Show in rows" option
As you can see, the measures are displayed as values in the columns. To display them at the row level, we need to enable an option in the Formatting area. To do this, select the matrix and click the Brush icon in the Visualization area. Scroll down to the Values section and enable the Show in Rows option. In the new formatting panel you will find this option under the Layout section.
Once you have activated this option, the measures are automatically displayed on the X axis:
Step 3: Format the matrix according to your wishes
Now you can format the matrix according to your needs. For example, you can change the order of the measures by rearranging them in the Columns area. You can also adjust the font size, color and alignment of the cells. You can also add or remove headers and totals rows.
Conclusion
In this blog post, I showed you how to display Measures in a matrix in Power BI at row level. This is a simple and useful option to present your data in a clearer and more fitting way.
