As usual, we start with a dataset.
Step 1: Create a Pivot Table Using the Data Model
Our first step is to create a Pivot Table, but not just a regular Pivot Table. We want to incorporate the use of the Data Model with this Pivot Table to give the result additional abilities.
Our dataset is a proper Excel Table named “Orders”.
We click anywhere in the table and select Table Design (tab) -> Tools (group) -> Summarize with Pivot Table.
In the PivotTable dialog box, leave all the existing settings as-is with the addition of checking the box labeled “Add this data to the Data Model” and click OK.
Now we can work with a “Power Pivot” Table instead of a regular Pivot Table.
(Are you starting to feel superior to those regular Pivot Table users?)
Featured Course
Black Belt Excel Package
Step 2: Create KPI Measures in the Data Model
Now that we have loaded our data into the Data Model, we will use this to create Measures in the model that will help us control the KPI Slicer.
“Number of Orders” KPI Measure
Select Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure.
In the Measure dialog box, we will create the KPI formulas we need to be followed by the creation of the Slicer.
Our first KPI will be called “Number of Orders” and use the following formula:
=COUNTROWS(Orders)
After clicking OK, we see the “Number of Orders” KPI added to the exiting Power Pivot Table showing the total number of all orders.
Let’s subdivide this total by placing the “Company Name” field in the Rows zone.
“Distinct Count of Product” KPI Measure
Select Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure.
Our second KPI will be called “Distinct Count of Product” and use the following formula:
=DISTINCTCOUNT(Orders[Product])
After clicking OK, we see the “Distinct Count of Product” KPI added to the exiting Power Pivot Table showing the number of distinct items sold by each company.
“Total Quantity” KPI Measure
Select Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure.
Our third KPI will be called “Total Quantity” and use the following formula:
=SUM(Orders[Quantity])
After clicking OK, we see the “Total Quantity” KPI added to the exiting Power Pivot Table showing the total number of ordered items sold by each company.
NOTE: Realizing that the numbers appear a bit bland without any formatting, we will hold off on the pretty for a bit and take care of it later.
Step 3: Create the Slicer Source
We want to represent the column headings of the Power Pivot Table as the text on the Slicer buttons.
Since we only have three items, and our list is static, we’ll avoid the complexity of creating a dynamically updating list and simply type a small list of what we want to be displayed on our Slicer buttons.
Format the newly created list of Views as an Excel Table (CTRL-T) and name the table “View”.
Step 4: Add the “View” to the Data Model
We need to add the newly created Excel Table named “View” to the Data Model.
To do this, with the “View” table selected, click Power Pivot (tab) -> Tables (group) -> Add to Data Model.
We see the “View” table added to the Data Model.
Close the Power Pivot window and return to Excel.
Step 5: Create the Slicer from the “View” Table in the Data Model
Now that we have added the “View” table to the Data Model, we can use it as a Slicer to control the filtering of the Power Pivot Table.
In the Pivot Table Fields panel, select the “All” category at the top and expand the “View” table to reveal the “View” field.
Right-click on the “View” field and select “Add as Slicer”.
Clicking the buttons on the Slicer does not affect the Power Pivot Table because we have yet to link the “View” Slicer to the “Orders” table in the Data Model.
Featured Course
Master Excel Power Query – Beginner to Pro
Step 6: Create a Measure to Control the Pivot Table Using the Slicer
Now that we have created our KPI Slicer, we need to create two Measures in the model: one that will detect which item in the Slicer has been selected by the report viewer, and another to enforce the selection.
“Slicer” Measure
Select Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure.
In the Measure dialog box, our measure will be created in the “View” table and be called “Slicer” and use the following formula:
=VALUES(View[View])
After we click OK, we can see that the Slicer’s single selection is displayed for each of the “Company Names”.
NOTE: If you are receiving an error when adding the Slicer “View” to the Values zone in the Power Pivot Table, make sure you have only a single button selected in the slicer. Selecting more than one button in the Slicer will result in the following error.
To combat this issue of multiple selections in the Slicer, we will change the formula driving the “View” Slicer to ignore multiple button selections.
Select Power Pivot (tab) -> Calculations (group) -> Measures -> Manage Measure.
Select the “Slicer” measure and click Edit.
To ensure that only one Slicer button (i.e., condition) is selected, update the formula as follows:
=IF(HASONEVALUE(View[View]), VALUES(View[View]) )
What this does is check to see if a single item is selected from the “View” field. If only one item is selected, then the “View” field is processed. Otherwise, if we hold down CTRL and select multiple Slicer buttons, nothing happens.
When tested, selecting more than one button in the Slicer yields nothing in the table.
“KPI” Measure
Select Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure.
In the Measure dialog box, our measure will be created in the “Orders” table and be called “KPI” and use the following formula:
=SWITCH([Slicer], "Number of Orders", [Number of Orders], "Product Count", [Distinct Count of Product], "Quantity Sold", [Total Quantity])Set all the formatting options as displayed below.
Update the Power Pivot Table VALUES Zone
We next need to update the Power Pivot Table to only contain the KPI measure in the VALUES zone.
Remove the existing entries in the VALUES zone leaving only the KPI measure.
The result is a Power Pivot Table like the following.
Step 7: Add the Chart
Now it’s time to create the chart that will dynamically display and react to our KPI Slicer selection.
Click anywhere in the Power Pivot Table and select Insert (tab) -> Charts (group) Clustered Column Chart.
The inserted chart appears as follows:
Perfecting the Look of the Chart
Let’s apply the following cosmetic changes to the chart to make it more visually appealing:
- Remove the Legend
- Remove the Chart Title
- Add Data Labels
- Remove the Major Horizontal Gridlines
- Remove the Vertical Axis
- Make the Horizontal Axis labels Bold
- Hide the Field Buttons (PivotChart Analyze (tab) -> Show/Hide (group) -> Field Buttons -> Hide All)
- Add Data Labels (right-click any bard and select Add Data Labels)
- Reduce the size of the Plot Area to provide some space above the chart. This area will be used to display the Slicer buttons.
Step 8: Customize the Slicer and Integrate it with the Chart
Select the KPI Slicer and apply the following customizations:
- Bring the KPI Slicer to the front-most layer (right-click and select Bring to Front)
- Set the Columns option to 3
- Remove the Slicer heading (right-click -> Slicer Settings -> uncheck Display Header)
- Resize to produce the look below
Removing the Slicer’s Border
As there is not a single-click method to hide the blue border surrounding the slicer, and we want the buttons to appear as if they are floating above the chat columns, we must duplicate and modify the existing Slicer Style then apply the modified style to the Slicer.
- Right-click on the highlighted Slicer Style and select Duplicate.
- Give the new Slicer Style a name, such as “Slicer – No Border”.
- Select the Slicer Element “Whole Slicer” and click Format.
The updated Modify Slicer Style dialog box should appear as follows:
Clicking OK will commit the updates but the newly created Slicer style will not be applied to the existing Slicer.
To assign the newly created Slicer style to the existing Slicer, select Slicer (tab) -> Slicer Styles (group) -> “Slicer – No Border”.
The updated chart looks much better with the new Slicer style.
As an extra bit of chart polish, let’s sort the columns in the chart in descending order.
Right-click any column in the chart and select Sort -> Sort Largest to Smallest.
Testing the Slicer
Click the various Slicer buttons to test the functionality.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
