This week at The Data School we’ve learnt a new data wrangling and visualisation tool – Power BI. When trying to build a dynamic viz, I found the tip to swap metrics very useful. The function is similar to using parameters to swap measures in Tableau. In this blog I’ll cover how to use this technic in Power BI.
1. Create a new table for the metrics you wish to swap
First, select “Enter Data” to create a new table. Type in the metrics you wish to swap between in one column. Give this new table a name. Then select “Load”. Now the new table you just created should appear in the Fields list.
2. Create calculations to swap metrics
Next, right click on the new table you just created, and select “New measure” to create a new calculation (called “DAX“).
The first DAX function is “SELECTEDVALUE”. This will return the value in your metrics table and will be used in the next SWITCH function.
Next we’re creating a SWITCH calculation to tell Power BI what values we wish to swap when selecting different metrics. When typing the calculation, a prompt will show up to give you a hint on the formula.
The whole calculation is shown below. This is saying:
- When I select the measure “Total Sales”, give me [Total Sales] value in my ‘Metrics’ table. (This is not the table we created in step 1. This is another table in my dataset with values I’d like it to return.)
- When I select the measure “Total Profit”, give me [Total Profit] value in my ‘Metrics’ table.
- When I select the measure “Average Margin”, give me [Avg Margin] value in my ‘Metrics’ table.
- Finally if nothing is selected, give me [Total Sales] value in my ‘Metrics’ table by default.
In the above formula:
- Words in light blue capital are DAX function (SWITCH, TRUE).
- Words in dark blue are table names (‘Metrics Table’, Metrics. Single quotes appear in the ‘Metrics Table’ because there’s a space in the table name).
- Words in purple with brackets are column names in your table ([Selected Value], [Total Sales], [Total Profit], [Avg Margin]).
- And words in brown with double quotes are text.
3. Create the viz
Now we have all calculation ready to build the interactive viz. First of all, we need a slicer for users to choose from different metrics. To do that, select “Metric” under the Metrics Table that we’ve created in step 1. Then in Visualizations, select the second one on the fifth row, which indicates slicer chart type. Then select the brush icon to format the slicer. Under the General tab, select Horizontal orientation.
Now your slicer should look like this. To remover the title “Metric”, click on the chart and go to Format (the brush), then turn off Slicer header.
The the next step is to create a dynamic chart. To do that, select “Swap Metrics” that we created in step 2 as our measure, and a dimension that you wish to show on you viz. In this example I use “Country”. Then choose a viz type. I’ll use horizontal bar chart in this case.
Now your viz should look something like this. And it’s already interactive!
The last step is a bit formatting to make the viz look nicer. We can first remove axis title “Swap Metrics” in Format–> X axis–> Axis title. Then to make the chart title dynamic, go to Title tab under Format. In Title text, select fx icon. In the pop-up window, select the field you wish it to be based on. In this example, we can select “Metric” (the value in our slicer).
Here you have it! Now you can swap your viz by clicking different metrics in the slicer.
