Choose a metric to display on a graph in Power BI


Introduction

Sometimes it becomes necessary to provide the user with the ability to choose which metric to display on the graph for detailed analysis.

In Power BI, this can be done quickly and easily by creating a helper table, calculated measure, and a regular slicer.

Description of the task

We work with the already familiar dataset Sample superstore, which contains data on store sales in the United States for 2015-2018.

It is necessary to build a graph that, depending on the choice, would display the dynamics of one of the indicators – Sales, Profit, Profit Ratio and Average Discount Rate – over time.

The result that we get in the end

Download pbix file MeasureSwitchFunction you can by link

Let’s start developing

Step 1. Let’s create measures that can be selected in the slicer.

1_Sales = 
    SUM(Orders[Sales])    
2_Profit = 
    SUM(Orders[Profit])
3_Profit Ratio = 
    DIVIDE(
        SUM(Orders[Profit]),
        SUM(Orders[Sales])
    ) * 100
4_Average Discount Rate = 
    AVERAGE(Orders[Discount]) * 100

Since the Profit Ratio and Average Discount Rate metrics are measured in percentages, we multiply these values ​​by 100 for the convenience of reading the Y axis, and then add the% symbol in the label of this axis.

Advice: use DIVIDE instead of the “/” division operator, as this function handles division by zero automatically.

Step 2. Let’s create an auxiliary table, on the basis of which the measure will be calculated afterwards, and also the slicer will be built.

  • On the toolbar, go to the tab Home, then select Enter Data

  • In the window that appears Create Table create a table with 2 columns: ID – for easy reference to metrics in subsequent formulas, Name – to display the name of metrics in the slicer. Next give table name – for example, Metric Name. Click on Load to load this table into the data model.

Step 3. Let’s create a measure that, depending on the metric selected in the slicer, will refer to one of the measures created in Step 1: 1_Sales, or 2_Profit, or 3_Profit Ratio, or 4_Average Discount Rate.

Selected Measure = 
    SWITCH(
        SELECTEDVALUE('Metric Name'[ID],1),
        1, Orders[1_Sales],
        2, Orders[2_Profit],
        3, Orders[3_Profit Ratio],
        4, Orders[4_Average Discount Rate]
    )

Step 4. Let’s create a measure that will dynamically change the label of the Y axis depending on the metric selected in the slicer.

Selected Measure Title = 
    IF(
        OR(SELECTEDVALUE('Metric Name'[ID]) = 1, SELECTEDVALUE('Metric Name'[ID]) = 2),
        SELECTEDVALUE('Metric Name'[Name]) & ", $",
        SELECTEDVALUE('Metric Name'[Name]) & ", %"
    )

Sales and Profit are measured in dollars, so we concatenate the name of the metrics with the $ symbol to label the Y axis. The Profit Ratio and Average Discount Rate values ​​are measured in percent, therefore, to label the Y axis, we concatenate the name of the metrics with the% symbol.

Step 5. Add the Slicer visualization type to the report page, built on the basis of the Name field from the Metric Name table. Select the horizontal type in the Format -> General settings.

Clue: In order for the metrics in the slicer to be displayed not in alphabetical order (AZ), but in the order of their ID (1-4), select the Name field, on the toolbar, go to the Column tools tab, in the Sort by Column settings, select ID instead of Name.

Step 6. Add the Selected Measure line chart by Order Date (Month, Year) to the report page.

Step 7… Let’s set up a dynamic Y-axis label. Since Power BI doesn’t have the ability to conditionally set up axis labels, this function will be performed by the Title located in the upper left corner relative to the graph.

  • After selecting the line chart, go to the chart formatting settings, turn on Title, click on the function icon fx

  • In the window that appears, in the Format Style field, select Field Value, and in the Which field should we base this on? select the Selected Measure Title calculated in Step 4.

Step 8. We test and validate the steps implemented above.

Step 9. We format the visualizations in accordance with the taste preferences / brand book of the company.

Even more useful information

Join the telegram channel Informed By Data, there you will find a lot of useful information about data analytics, visualization and intelligent data manipulation.

Similar Posts

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *