Function PIVOTBY
Pivot tables have been with us for a long time and allow us to easily analyze data by changing the perspective from which we look at them. Recently, there is also a PIVOTBY function that allows us to get a similar report, but in an Excel spreadsheet. By marking rows, columns, values and other parameters, a dynamic report is generated. Here’s a post about how to use this function…
The syntax of the PIVOTBY function is as follows:
PIVOTBY(<rows>,<columns>,<values>,<f>,<headers>,<depth-r>,<sort-r>,
<depth-c>,<sort-k>,<filter>,<rel>)
The simplest way to generate a pivot report in the form of a dynamic table is if you just specify the dimension (column) to be displayed in the rows, the dimension to aggregate and the aggregation function. For example, if you write the formula:
=PIVOTBY(Transactions[CLASSIFICATION],,Transactions[AMOUNT],SUM)
A table showing sales by classification with the sum of all amounts will be displayed.
If you want to change the table so that it also shows manufacturers in the columns, just modify the formula:
=PIVOTBY(Transactions[CLASSIFICATION],Transactions[MANUFACTURER],
Transactions[AMOUNT],SUM)
The result can be seen in the table.
After specifying the aggregation function, it is possible to provide an argument that defines whether headers are present and displayed. Then an argument is specified which determines the calculation and display of subtotals, as well as sorting, at the row and column level.
What if you want to display all item names as well as total sales amount for the brand “BAJADERA”? In that case, you should write a formula like this:
=PIVOTBY(Transactions[ITEM NAME],,Transactions[AMOUNT],
SUM,,,,,,Transactions[BRAND]=”BAJADERA”)
The result is shown in the table:
The last argument of this function is used to specify against which the aggregation is performed, rows or columns.
As you can see, the PIVOTBY function returns dynamic tables that facilitate data analysis, and you judge whether this is a more efficient way than creating traditional pivot tables.