• Maciek Lasota

How to declutter your PowerBI report

Updated: Mar 8

Quick guide to slicers for dynamic measure change.

Clean Power BI report

If you have a large number of measures in your report and you are running out of space this can be very helpful to clean up your report. Instead of showing a separate chart for all of your measures and cluttering your report just use SWITCH() to create a slicer that will adjust what users see on one or more charts.

It's a common occurrence to see reports like the below where we see similar charts all in one report. It sometimes makes sense, but it does take up quite a bit of room.

PowerBI separate charts

What if we want to cut on space and dynamically flick over measures with slicer like on screen below:

PowerBI dynamic chart

In our use case we want to go from 3 separate charts to one that user can interact with, here`s how to implement this:

Step 0. Create your measures

First, we need to create a basic measure so we have something to work on.

You probably already have your measures but for this example, I used the PowerBI sample financials dataset and created three basic measures so we have something to work on.

UnitsSold_Total = sum(financials[Units Sold])

Sales_Total = sum(financials[ Sales])

Profit_total = sum(financials[Profit])

PowerBI measure
PowerBI measure
PowerBI measure

Step 1. Create Measures Table :

You need to create a table with names of measures you want to show, the index can be added for sorting purposes. We use Enter Data:

Create table in powerBI

Creating table in powerBI

Make sure the created table doesn`t have any relations in your data model.

Step 2. Implement the switch function:

Create Measure_Select with SWITCH() function, using SELECTEDVALUE() specify measure table and provide your measures as arguments (in this case we provide 3 measures from Step 0 :

Switch function in PowerBI for slicers

Step 3. Put it into work on the dashboard:

- Create a slicer with Measure name as Values

- Create a chart with Measure_Select as Values from step2 as values and any other dimension you want to see I just added date.

Cleaning PowerBI report

Then in the option set up a single select property for slicer and you're good to go.

This technique can open up some possibilities and here are my two recent examples:

Use Case 1:

If you duplicate the measure table and do some naming adjustments (just rename measure to measure2) you can have a real clear scatter plot when you can choose out of your measures :

Scatter plot in PowerBI

In this case, I`m comparing Profit vs Units Sold but you can be very creative with this especially when you have few measures

Use Case 2:

It can be very handy on decomposition tree which consumes a lot of space:

Decomposition tree with slicer in PowerBI

Stay tuned and subscribe at the bottom or follow me on Twitter.

Suited Data Management Solutions

Solution Tailor

Blog about Data Management and Quality  Analytics Solutions.