One of the biggest challenges when developing administrative panels is to make good use of the space on the pages and, at the same time, to display the information in a complete and objective way. Therefore, in this post, we will teach an alternative to display distinct values ​​on a single chart using dynamic measures in Microsoft Power BI. This allows the user to change the measurement of the graphs of a report dynamically, through a filter-type visual or data segmentation.

First, we emphasize that this is the “old-school” method of solving this problem, without the need to use other tools. In one of the recent Power BI updates, it is possible to achieve this same result using “Calculation Groups” through dataset editing tools, such as the “Tabular Editor”. If you are looking for this solution, check out the Planilheiros video on the subject:

Power BI (Desktop) – Tabular Editor – YouTube 

The solution

For the creation of this example, we used the example database available in Power BI Desktop itself.

The developed solution is composed of:

  • three measures in DAX, containing the calculations we wish to present;
  • an auxiliary table, which will act as a dimension for the filter that selects the measure dynamically (that is, this table is basically a list with the names of the measures);
  • a dynamic measure, which will be used in the graph.

Creating measures in Power BI

After we imported the sample data into Power BI, we created 3 measures using the Financials table.

Gross price:
Gross price = SUM(financials[Gross Sales]) 

Net price:
Net price = SUM(financials[Sales])

Profit: 
Profit = SUM(financials[Profit]) 

Auxiliary table creation

As stated earlier, this table will be responsible for making it possible for the user to select the measurement. As such, it is nothing more than a list with the names of the measures you want to make available in your report. For the creation of the table, we use the manual data entry functionality, as shown in the image below:

Dynamic measure creation

We will create a measure called DynamicMeasure to, from this ID of the table TableMeasures, select the correct measure to present in the graph.

Dynamic Measure: 
DynamicMeasure =  

SWITCH( 

MAX(TableMeasures[ID]), 

    1, [Gross Price],  

    2, [Net Price],  

    3, [Profit]  

) 

Filter creation

Finally, just draw the report with the graphics you want and use the dynamic measurement where it is convenient. In order for the user to be able to make changes between the measurement options, we must provide the buttons that he will need for this. Our suggestion is to use a filter type object and configure it with the responsive display functionality. That way, it will appear as large radio buttons on the screen.

In the image below, we selected the filter view option and chose the MEASUREMENT field that contains the measurement name in the auxiliary table that we created.

Tip:
To format the filter leaving the appearance of a button, you must access the formatting guide and in the Orientation item select the Horizontal option.

In addition, another suggestion is to include a card-type look that will have the DynamicMeasure field as its field. Thus, for each selection made in the filter, we will have a change as it is displayed in the card look.

Tip:
To format the title also dynamically, according to the filter selection, just include the MEASURE column field in the title name formatting, as shown in the image below.

Result

See below the final result of the report produced for this post. In it, we put three more different visualizations (a column chart, a treemap and a map), all using the dynamic measure we created. Note that, using the buttons in the upper right corner, we can easily change the measurement displayed by all views.

You now know how to build dynamic measures for Power BI visualizations. Also find out how to configure RLS in Power BI!