Schalt Berechnungen an engem PivotTable mat 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 Entwéckler - Knäppchen COM Add-ins (Entwéckler - COM Add-ins)
  • Datei - Optiounen - 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:

Schalt Berechnungen an engem PivotTable mat 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 oder Team Home - Format als Dësch (Home - Format als Table) and it is desirable to give them sane names on the tab Builder (Design). Let it be, for example, Sales и Servicer.

After that, each table in turn needs to be loaded into the Data Model – for this we use on the tab powerpivot Knäppchen 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 Mesure. 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:

Schalt Berechnungen an engem PivotTable mat 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 MARKEWÄERTER 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 MARKEWÄERTER 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:

Schalt Berechnungen an engem PivotTable mat slicers

Loosst eis et Stéck fir Stéck opbriechen:

  1. Funktioun WIESSEL – 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. Funktioun 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 – ROND.
  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 der Haaptentrée tab select command Resumé Dësch (Home — Pivot Table).

dann:

  1. We throw the field Produit from the table Sales an d'Géigend Rows (Reien).
  2. Throwing a field there Resultat from the table Servicer.
  3. Right click on the field Resultata wielt eng Equipe Add as slice (Add as Slicer).
  4. Throwing the second measure Conclusioun from the table Servicer an d'Géigend Wäerter (Wäerter).

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.

Schéinheet 🙂

  • Virdeeler vum Pivot vum Datemodell
  • Plan-fact analysis in a pivot table on Power Pivot
  • Create a database in Excel using the Power Pivot add-in

 

Hannerlooss eng Äntwert