3 easy steps to create Dynamic Measure Columns in Power BI Matrix Visuals Using Power Query and Slicers

Power BI is a platform that allows you to connect to various data sources, transform and model your data, and create interactive dashboards and reports. Power BI can help you gain insights from your data and communicate them effectively.

What is a Dynamic Measure Column?

A dynamic measure column is a column that can change its values and format based on a user’s selection or filter. In Power BI, dynamic measure columns can be created by using slicers, calculation groups or DAX expressions. Dynamic measure columns allow users to customize and compare different metrics in a single visual, such as a matrix. For example, a dynamic measure column can show sales, profit, discount and quantity for different products and regions, depending on the user’s choice.

Creating the Dynamic Measure Column

We will be making this matrix having Dynamic Measure Column:

Dynamic Measure Column to show only Discount for different segment

Dynamic Measure Column to show only Discount and Profit for different segment

As you can see we are selecting the measure column we want to show in the matrix using the slicer, making our matrix dynamic. To do this we need to create a duplicate of our table so that original table remains intact, in case we need it for other purposes otherwise you can do the operations on your main table too. All our tasks will be done in Power Query so we will head their first.

1.Creating a duplicate of our table:

As mentioned before this is an optional step but it is a good practice to do so in case we are affecting the columns of our main table. To do it we need to go to Power Query for which click on Home at the top and press Transform data in Queries.

Click Transform data in Queries

Now right click your main table on left side in Queries and select Duplicate.

Select Duplicate to create a copy of your main table

Now you can change its name by right clicking the duplicate table then selecting Properties and in Name section of Query Properties typing the new name.

Rename the table in the Name section

2.Formatting the columns of our table:

Now select the categorical columns and the numerical columns that you want to show in your matrix using Ctrl or Cmd on Macs then press on Remove Columns at the top to show a dropdown and select Remove Other Columns from it. This will keep only the columns that you selected in the table and remove the unselected ones.

Choose Remove Other Columns to keep only selected columns

I had selected Segment, Category and Sub-Category for categorical columns and Sales, Quantity, Discount and Profit for numerical columns.

This is how the table looks like

Now select all the categorical columns and while the columns are selected right click any one of the columns and choose Unpivot Other Columns.

This will remove the numerical columns and replace it with two new columns — Attribute and Value. What happened here is that for every entry in the row which earlier had a separate column for measures like Sales and Profit now has four entries in the row with Attribute displaying what is the measure and Value giving the corresponding value.

The table with Attribute and Value columns

Now double click on where there is written Attribute and rename it as Measure Column.

Rename Attribute to Measure Column

In the right hand side you can find all the steps which you have applied on the table and you can hover over the step and press on cross to undo that step.

The Applied Steps

Finally at the top press at the extreme top right click Close & Apply and select Close & Apply.

Select Close & Apply

3.Creating our matrix and slicers:

Now get a Matrix from Visualizations and add Segment, Category and Sub-Category from Table1(2) which is my duplicate table in Rows, Measure Column in Columns and Value in Values.

Matrix settings

Click on + icon of Segment to expand them.

Click on + icon of segment to expand the Matrix

Now get a Slicer from Visualizations and add Measure Column to it.

Add Slicer from Visualizations

Select the Slicer, go to Slicer settings in Format your visual, in Style select Vertical list, then in Selection turn on Multi-select with CTRL.

Slicer settings

With this we are done, now whichever Measure Column you select on the slicer will be displayed in the matrix. To select multiple columns hold Ctrl or Cmd on Mac and select the columns.

Both Profit and Sales selected

To make Professional KPI refer to this link.

To create a conditional formatted Area Chart refer to this link.

To find max point on a line graph refer to this link.

To create a current week sales KPI refer to this link.

To create a Dynamic Title refer to this link.

To read more stories like this you can follow me with this link.

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass

References:

1. https://community.fabric.microsoft.com/t5/Desktop/Create-a-dynamic-measures-using-field-parameter-measure-values/td-p/2994665
2. https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
3. https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/