How to Show Measures in Rows in Power BI

Arranging measures in rows instead of columns is one of the most common requests from users building reports in Power BI, especially those coming from an Excel background. In an Excel PivotTable, it's trivial to drag a value field and display it as rows. Power BI, however, needs a bit more convincing. This guide will walk you through the most effective methods to display your measures vertically in rows, giving you the report layout you need.

Why Show Measures in Rows? (The Excel Habit)

In countless business reports, stacking key performance indicators (KPIs) vertically is standard practice. Imagine a report where you want to compare Total Sales, Cost of Goods Sold, and Gross Profit for each month. The most intuitive way to read this is often with the KPIs listed in rows and the months spreading across as columns.

Here’s what Power BI does by default versus what you’re likely trying to achieve:

• Default Power BI layout (Measures in Columns): Easy to create by dragging measures into the "Values" field of a matrix or table, but can become wide and difficult to scan if you have a lot of measures.
• Desired Excel-like layout (Measures in Rows): Cleaner for comparing a list of metrics across categories. It's compact, scannable, and familiar to many stakeholders.

The core challenge is that Power BI’s data model is designed to aggregate numbers in columns by default. Measures belong in the "Values" data role, which renders as columns. To get them into rows, we need to create a new "column" that contains the names of our measures and then use some DAX magic to swap in the correct value for each row. Let's explore the two best ways to do this.

Method 1: Using a Disconnected Table and the SWITCH() Function

This is the classic technique. It's quick to set up and doesn't require any external tools. The concept involves creating a small, helper table that lists your measures. This table isn't related to your main data tables, which is why it's called a "disconnected" table. Then, you write a single "master" measure that dynamically picks which calculation to show based on the row context.

Step 1: Create a Disconnected "Measures" Table

First, you need a table that contains the names of the measures you want to display. This will become the labels for your rows.

1. In the Power BI Desktop ribbon, click on the Home tab and select Enter data.
2. A window to create a new table will appear. Create two columns: one for the names of your metrics (e.g., "Metric Name") and another for a sorting order (e.g., "Sort Order"). The Sort Order column is crucial for controlling the display order later.
3. Populate the table with your measure names and desired sort order. For example:

1. Give the table a name, like "KPIs" or "Measure Selector," and click Load. Remember, do not create any relationships between this new table and your other data tables in the model view.

Step 2: Create a Central "Dispatcher" Measure with DAX

Now, we'll write a single DAX measure that acts as a switcher. It checks which measure name is active in a given row (from our new 'KPIs' table) and then returns the result of the corresponding, pre-existing measure in your model.

For this to work, you must already have your base measures created (e.g., [Total Sales], [Total Expenses], etc.).

1. Right-click on any table in the Data pane and select New measure.
2. Enter the following DAX formula, customizing it with your table/column names and measures:

Selected KPI Value = 
VAR SelectedMetric = SELECTEDVALUE('KPIs'[Metric Name])
RETURN
SWITCH(
    TRUE(),
    SelectedMetric = "Total Sales", [Total Sales],
    SelectedMetric = "Total Expenses", [Total Expenses],
    SelectedMetric = "Profit", [Total Profit],
    SelectedMetric = "Profit Margin", [Profit Margin],
    BLANK() 
)

This code snippet first grabs the metric name from the currently active row using SELECTEDVALUE(). Then, the SWITCH() function checks that name against a list of possibilities and returns the value of the correct measure.

Step 3: Build the Matrix Visual

Now, let's put it all together in a report.

1. Add a Matrix visual to your report canvas.
2. From your new 'KPIs' table, drag the Metric Name column into the Rows field.
3. From your fact or dimension tables, drag a category like 'Fiscal Year' or 'Product Category' into the Columns field.
4. Drag your new [Selected KPI Value] measure into the Values field.

Your matrix should now display the measure names in rows!

Step 4: Sorting Your Measures Correctly

You'll probably notice your measures are sorted alphabetically, not in the logical order you wanted. This is where our 'Sort Order' column comes in.

1. Go to the Data View in Power BI Desktop.
2. Select your 'KPIs' table.
3. Click on the Metric Name column header to select it.
4. From the Column tools ribbon at the top, click Sort by column and select Sort Order.

Return to your report canvas, and the measures in your matrix will now be in the correct order you defined.

A Note on Formatting

A limitation of the SWITCH() method is formatting. Since one measure is returning different kinds of values (currency, percentages, whole numbers), you can't set a single format string. One common workaround is using the FORMAT() function within your SWITCH() statement, like FORMAT([Profit Margin], "0.0%"). However, be aware that FORMAT() converts your numbers into text, which can prevent you from using them in other calculations or charts.

Method 2: Using Calculation Groups (The Pro Method)

While the SWITCH() method is great, calculation groups are the more robust, scalable, and powerful solution. They are designed for this exact purpose and solve the formatting problem elegantly. The one catch is that, as of now, you need an external tool like Tabular Editor (available for free) to create them.

Step 1: Set Up Tabular Editor

1. In Power BI Desktop, go to File > Options and settings > Options.
2. Under Global, select External tools. Ensure "Allow external tools ..." is checked.
3. Download and install Tabular Editor (version 2 or 3). Once installed, it will appear in your External Tools ribbon in Power BI Desktop.

Step 2: Create a Calculation Group

With your Power BI file open, launch Tabular Editor from the External Tools ribbon. It will connect directly to your data model.

1. In the Tabular Editor window, right-click on the Tables folder in the left-side explorer pane.
2. Choose Create New > Calculation Group. Name this new group something descriptive, like "Report Metrics".
3. Click on the new calculation group, and in the properties pane, change the name of its column from "Name" to something clearer, like "Metric". This is the column you'll use in your visuals.

Step 3: Define Calculation Items for Each Measure

Think of calculation items as the different rows you want to display.

1. Right-click the new "Report Metrics" calculation group on the left side, then New Calculation Item. Give it the name of the first measure (e.g., Sales).
2. With the new calculation item selected, set the formula in the properties pane to use SELECTEDMEASURE(). Define the properties for both Format and the calculation.
3. In the properties of each calculation, add your DAX expression. For 'Total Sales' calculation item, you might use [Total Sales] without any SWITCH or VAR statements. In the FormatStringExpression box, set your desired format, e.g., "[#,]##$" for Sales or "0%" for Profit Margin.

Repeat this process for each row you want to display. Save your changes in Tabular Editor (click the Save icon) to write the model back to your Power BI file.

Step 4: Using a Calculation Group in a Visual

The beauty of this method is how it simplifies building visuals.

1. A new table ("Report Metrics") will appear in your fields pane with a single column named "Metric". Drag "Metric" to the Rows area of your Matrix visual.
2. Drag one of your base measures (e.g., [Total Sales]) to the Values field. The calculation group context will override the base measure's value and format in displaying the correct calculation.

Advantages of Calculation Groups

• Scalability: Easy to add new measures by just adding a new item in Tabular Editor without complex SWITCH statements.
• Formatting Control: Each row can have its own unique format (currency, percentage, etc.) without compromising the major pivot of the first method.
• Reusability: You can use the same calculation group across multiple visuals and report pages.

Final Thoughts

Achieving your goal of displaying measures in rows in Power BI doesn’t have to be more work than expected. While the straightforward SWITCH method is quick and easy, the more robust and scalable calculation groups approach elevates your data reports into more interactive and visual layouts. Embrace these techniques to help transform your data reporting into formats you and your stakeholders prefer.

If you feel that managing large data transformations should be uncomplicated, then connecting your marketing and sales data sources like Google Analytics, Salesforce, or SAP and centralizing into one cohesive report or dashboard using a tool like Graphed could be an incredible choice. Make the entire report creation process seamless with the right advance planning and deployment using DAX, writing advanced function techniques, and further expanding your skills.