Префрлување на пресметките во Стожерна табела со сечачи

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:

  • Табот инвеститорот - копче COM додатоци (Програмер - COM додатоци)
  • Датотека – Опции – Додатоци – COM додатоци – Оди (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 или тим Дома – Форматирајте како табела (Дома - Формат како табела) and it is desirable to give them sane names on the tab Изведувач (Дизајн). Let it be, for example, Продажба и Услуги.

After that, each table in turn needs to be loaded into the Data Model – for this we use on the tab powerpivot копчето. Додај во модел на податоци (Додај во модел на податоци).

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

The calculated fields in a PivotTable by Data Model are called мерки. 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 ВРЕДНОСТИ 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 ВРЕДНОСТИ 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:

Префрлување на пресметките во Стожерна табела со сечачи

Ајде да го разложиме дел по дел:

  1. функција ПРЕКИНУВАЧ – 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. функција 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 – ТРКАЛЕЗНА.
  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 Главните tab select command сумарна табела (Home — Pivot Table).

потоа:

  1. We throw the field Производ од масата Продажба до областа Редови (Редови).
  2. Throwing a field there Резултат од масата Услуги.
  3. Right click on the field Резултати изберете тим Add as slice (Add as Slicer).
  4. Throwing the second measure Заклучок од масата Услуги до областа Вредности (вредности).

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.

Убавина 🙂

  • Придобивките од 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

 

Оставете Одговор