Suia fa'atatau ile Pivot Table ma fasi fasi

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:

• Taofi atinae - faʻamau COM fa'aopoopoga (Fa'atupu - 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:

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 po o le au Aiga – Fa'atulagaina e pei o se laulau (Aiga — Fa'atulagaina e pei o le Laulau) and it is desirable to give them sane names on the tab Fausia (Mamanu). Let it be, for example, Sales и auaunaga.

After that, each table in turn needs to be loaded into the Data Model – for this we use on the tab Malosiaga faʻamau Fa'aopoopo i Fa'ata'ita'iga Fa'amatalaga (Fa'aopoopo i Fa'ata'ita'iga Fa'amatalaga).

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

The calculated fields in a PivotTable by Data Model are called fua. 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:

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 AGA TAUSILI 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 AGA TAUSILI 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:

Sei o tatou vaevaeina i lalo vaega ma lea vaega:

1. galuega tauave suiga – 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. galuega tauave 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 – LAU.
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 O le autu tab select command laulau otooto (Home — Pivot Table).

Ona:

1. We throw the field mea e gaosi mai le laulau Sales i le eria Lalo (laina).
2. Throwing a field there iʻuga mai le laulau auaunaga.
3. Right click on the field iʻugama filifili se au Add as slice (Add as Slicer).
4. Throwing the second measure Faaiuga mai le laulau auaunaga i le eria Taunuʻuga (Fa'ataua).

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.

Lalelei 🙂

• Fa'amanuiaga ole Pivot ile Fa'ata'ita'iga Fa'amaumauga
• Plan-fact analysis in a pivot table on Power Pivot
• Create a database in Excel using the Power Pivot add-in