Interactive Dashboard Trick

If you’ve ever created a Pivot Table or Pivot Chart, you know how easy it is to aggregate table entries by some criteria, like “unit sold” or “total sales”.

You may have even added a Slicer to bring a sense of interactivity to your chart.

The Slicer is merely a fancy filter that reduces the whole down to a subset.

What if you want to use the Slicer to alter the aggregation method?

In other words, wouldn’t it be cool to use a Slicer to switch the aggregation method, say between the total, the count, or the average of sales?

Let’s see how this is done to add greater interactivity to your reports and dashboards.

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?)

All excel courses Leila Gharani

Master it ALL!

Whether you're a beginner or advanced Excel user, there is a course for you in our academy

GET ACCESS

Step 2: Creating 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: Creating 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.

Power Query course Leila Gharani

Master Excel Power Query Course

Beginner to Advanced (including M) 

Collect, Combine and Analyze Data with Ease - Create Pivot Tables with Data Model.

GET ACCESS

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: Creating 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.

  1. Right-click on the highlighted Slicer Style and select Duplicate.
  2. Give the new Slicer Style a name, such as “Slicer – No Border”.
  3. 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.

Excel Download Practice file

Master Power Pivot & DAX

Power Pivot & Data Modelling is a skill you need to have if you analyze data and create reports.

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Get Access