# Ex17#Adding Slicers to a Power BI Report

### Introduction

In Power BI, the filters option is used to drill down on a particular chart. But sometimes you want a selection to be applied to all the visuals in the report. Slicers are the on-canvas filters that get applied to all reports or charts in the page. This helps in interacting dynamically with the Power BI report. This guide will demonstrate how to utilize the slicer option in Power BI desktop.

### Data

In this guide, you will work with a fictitious data set of bank loan disbursal across years. The data contains 3,000 observations and 17 variables. You can download the dataset. The variables to be used in this guide are described below:

1. `Loan_disbursed`: Loan amount (in US dollars) disbursed by the bank.
2. `Date`: Date of loan disbursal.
3. `Purpose`: Purpose for which loan was disbursed.
4. `Interest_rate`: Annual interest rate charged on the disbursed loan.
5. `Weeknum`: Week number when the loan was disbursed.

Start by loading the data.

### Loading Data

Once you open the Power BI Desktop, the following output is displayed.

![b1](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/16c4cce1-5d1e-4c00-a4c0-34ee7dd58ccb_b1.png)

Click on **Get data** option and select **Excel** from the options.

![b2](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/044933be-9a35-4730-8384-d3c1eb1850cb_b2.png)

Browse to the location of the file and select it. The name of the file is **BIdata.xlsx**, and the sheet you will load is **BIData** sheet. The preview of the data is shown, and once you are satisfied that you are loading the right file, click **Load**.

![b3](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/0b295b3c-a9d4-4d8b-aea7-d84d9c14f75b_b3.png)

You have loaded the file, and you can save the dashboard. It is named **PowerBI Visualization**. The **Fields** pane contains the variables of the data.

![b4](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/7e56fe50-9d78-42b5-b82e-7ee3cdf26a86_b4.png)

### Adding Visualization

To begin, you will need a chart, table, or matrix. You will create a **line and stacked column chart** in this guide. You can locate it in the **Visualizations** pane. Click on the chart and it will create a chart box in the canvas. Nothing is displayed because you are yet to add the required visualization arguments.

![s1](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/59895bb6-1460-4723-89e5-8ae149e2fa24_s1.png)

You can resize the chart on the canvas, and the next step is to fill the visualization arguments. Drag the `Date` variable into the **Shared axis** field, and `Loan_disbursed` in the **Column values** field.

![s2](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/45e59477-2327-477c-b287-aa2922449c7c_s2.png)

Drag the `Interest_rate` variable into the **Line values** field.

![s3](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/0fd20b3a-6b0d-458d-af46-11c67d384a95_s3.png)

You can see that the interest rate values are adding up, which is incorrect. To change it to average, right click and select the **Average** option.

![s4](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/451388fc-40bb-40e5-83a8-aa333d6fe11a_s4.png)

The above steps will create the desired chart.

![s5](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/8722f1de-6746-4927-ab96-4050ccad6d79_s5.png)

You can format the chart and turn on **Data labels** as shown below. This displays the values in the chart.

![s6](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/089640e8-f1f4-4c00-9c29-81697e801524_s6.png)

To understand the functionality of slicers better, you can add one more chart. This time you will create a tree map chart. Start by clicking on the **Treemap** option under **Visualizations**.

![s7](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/491c468b-c822-4dce-8ae3-31053d920895_s7.png)

Drag the variable **Weeknum** into **Group** field, and `Loan_disbursed` into the **Values** field.

![s8](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/340fce27-e61a-44de-babc-cd061f3ccec1_s8.png)

You have the desired charts into the canvas, and you are now ready to explore the functionality of slicers.

### Adding Slicer to the Chart

To begin, click on the **Slicer** option that creates a slicer box in the canvas.

![s9](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/e7f6c76c-fdfd-42bf-a0a4-fc45de60dced_s9.png)

The slicer you will build is on the `Purpose` variable. The objective is to look at loan disbursal, interest rate and week number with respect to the `Purpose` variable. Drag the variable into the **Field** option as shown below.

![s10](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/4ac84039-77b6-43de-9f6c-7587fcba5e4b_s10.png)

You can see the slicer box with different categories of `Purpose` variable.

### Formatting the Slicer

Under the **Format** pane, you will see various options for formatting the slicer. The **Selection controls** option details how you can control the slicer display.

![s11](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/c74edfdb-19ce-49d3-a1ea-f7e8ff5cc9b0_s11.png)

Turn on the **Show “Select all” option** , and turn off the **Multi-select with CTRL** opton.

![s12](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/fa123c91-9de4-4930-9de4-2374169becab_s12.png)

If you want to display the charts on the canvas, only for the **Education** label of the `Purpose` variable, click on the label as shown below. This will change both the visuals in the canvas, which are now sliced to display only the **Education** category.

![s13](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/cc7035e3-f30f-4b4a-b8f5-25aeca4c7279_s13.png)

You can perform multiple selections of categories as shown below, which displays the visuals for categories, *Education*, *Home*, and *Personal*.

![s14](https://web.archive.org/web/20221217065418im_/https://pluralsight2.imgix.net/guides/ee405c02-f510-4238-84cc-04967381ad05_s14.png)

Now the charts titled **Loan\_disbursed and Average of Interest\_rate by Year** and **Loan\_disbursed by Weeknum** will slice the data and display the result for categories, *Education*, *Home*, and *Personal*.

### Conclusion

Slicing the data is an integral component of data manipulation and exploratory data analysis. To be able to visualize the different charts at the same time, with single or multiple slicers, is an important skill in descriptive and diagnostic analytics. This a common task and is sector agnostic in nature, which means it has applications across industries such as banking and financial services, manufacturing, utilities, ecommerce, retail, etc. This skill will improve your analytics and business intelligence capabilities.