Switching calculations in a PivotTable with slicers

Slicers in pivot tables can be used not only in the classic way – to filter the source data, but also to switch between different types of calculations in the value area:

Implementing this is very easy – all you need is a couple of formulas and an auxiliary table. Well, we will do all this not in the usual summary, but in the summary built according to the Power Pivot Data Model.

Step 1. Connecting the Power Pivot add-in

If the tabs of the Power Pivot add-in are not visible in your Excel, you will first need to enable it. There are two options for this:

  • Tab developer – button COM add-ins (Developer — COM Add-ins)
  • File – Options – Add-ins – COM Add-ins – Go (File — Options — Add-ins — COM-Add-ins — Go to)

If this does not help, then try restarting Microsoft Excel.

Step 2: Load Data into the Power Pivot Data Model

We will have two tables as initial data:

Switching calculations in a PivotTable with slicers

The first is a table with sales, according to which we will later build a summary. The second is an auxiliary table, where the names for the buttons of the future slice are entered.

Both of these tables need to be converted to “smart” (dynamic) with a keyboard shortcut Ctrl+T or team Home – Format as a table (Home — Format as Table) and it is desirable to give them sane names on the tab Constructor (Design). Let it be, for example, Sales и Services.

After that, each table in turn needs to be loaded into the Data Model – for this we use on the tab PowerPivot button Add to Data Model (Add to Data Model).

Step 3. Create a measure to determine the button pressed on the slice

The calculated fields in a PivotTable by Data Model are called measures. Let’s create a measure that will display the name of the pressed button on the future slice. To do this, in any of our tables, select any empty cell in the lower calculation panel and enter the following construction into the formula bar:

Switching calculations in a PivotTable with slicers

Here, the name of the measure comes first (Pressed button), and then after a colon and an equal sign, a formula to calculate it using the function VALUES DAX built into Power Pivot.

If you repeat this not in Power Pivot, but in Power BI, then the colon is not needed and instead VALUES you can use its more modern counterpart – the function SELECTEDVALUE.

We do not pay attention to errors in the lower part of the window that appear after entering the formula – they arise, because we do not yet have a summary and a slice in which something is clicked.

Step 4. Create a measure for the calculation on the pressed button

The next step is to create a measure for different calculation options depending on the value of the previous measure Pressed button. Here the formula is a little more complicated:

Switching calculations in a PivotTable with slicers

Let’s break it down piece by piece:

  1. Function SWITCH – an analogue of nested IF – checks the fulfillment of the specified conditions and returns different values ​​depending on the fulfillment of some of them.
  2. Function TRUE() – gives a logical “true” so that the conditions checked later by the SWITCH function work only if they are met, i.e. truth.
  3. Then we check the value of the button pressed measure and calculate the final result for three different options – as the sum of the cost, the average check and the number of unique users. To count unique values, use the function DISTINCTCOUNT, and for rounding – ROUND.
  4. If none of the above three conditions is met, then the last argument of the SWITCH function is displayed – we set it as a dummy using the function BLANK().

Step 5. Building a summary and adding a slice

It remains to return from Power Pivot to Excel and build a pivot table there for all our data and measures. To do this, in the Power Pivot window on The main tab select command summary table (Home — Pivot Table).

Then:

  1. We throw the field Product from the table Sales to the area Rows (Rows).
  2. Throwing a field there Result from the table Services.
  3. Right click on the field Resultand choose a team Add as slice (Add as Slicer).
  4. Throwing the second measure Сonclusion from the table Services to the area Values (Values).

Here, in fact, are all the tricks. Now you can click on the slicer buttons – and the totals in the pivot table will switch to the function you need.

Beauty 🙂

  • Benefits of Pivot by Data Model
  • Plan-fact analysis in a pivot table on Power Pivot
  • Create a database in Excel using the Power Pivot add-in

 

Leave a Reply