# Pivot Tables

> Pivot tables are one of the most useful widgets for visualizing data. They enable you to quickly summarize and analyze large amounts of data.

*Source: https://docs.sisense.com/main/SisenseLinux/pivot.htm*

---

Last updated: June 11, 2026

|  |  |
| --- | --- |
| [Tier](https://www.sisense.com/pricing/#pricing) | [Deployment](https://docs.sisense.com/main/SisenseLinux/introduction-to-sisense-cloud-managed-services.md#ComparisonofManagedCloudandSelfHosted) |
| Launch     Grow    Enterprise | Cloud     On-Prem |

Pivot tables are one of the most useful widgets for visualizing data. They enable you to quickly summarize and
analyze large amounts of data.

## Adding Data to a Pivot Table

Select the data to appear in the Pivot.

1. In the **Rows panel**, click **Add +** to select the field(s) whose values will be
   placed in the rows of the Pivot table.
     
   You can add up to 20 fields in the Rows panel.
     
   ![Pivot rows](https://docs.sisense.com/main/Resources/Images/pivot-rows.png)  
     

   **Note:**

   When Sisense is deployed on Linux, you can display HTML in your Pivot 2 cells as HTML or plain text, see
   [Configuring Pivot Table Limits](#Configur).

     
    When more than one Row is selected, the Rows are broken into sub-rows (groups). For example, the following
   shows **Condition** added as a second Row:
     
   ![Pivot rows](https://docs.sisense.com/main/Resources/Images/pivot-rows-2.png)
2. In the **Columns** panel, click **Add +** to select the field(s) whose values will be
   placed in the columns of the Pivot table. You can add up to 3 fields in the Columns panel.
     
   You can drag and drop the fields that you added to Columns to the Rows area and vice versa.
3. In the Values panel, select a field whose values will appear in the **Rows** and
   **Columns** of the Pivot table. You can add up to 20 measures to the Values panel.

   **Tip:**

   Right-click the value to add data bars to your pivot table.

   ![Pivot data bars](https://docs.sisense.com/main/Resources/Images/pivot-data-bars.png)

## Grand Totals and Subtotals

You can add Grand Totals and Subtotals to your tables, and define how to calculate Subtotals.

**Note:**

Users can now choose to also include subtotals for single records in pivot tables. This can be helpful when data will be downloaded for additional analysis and a consistent table structure is required.
To use this option, toggle on ‘Show subtotals for single rows’ in the Pivot Table section of the System Configuration page. (Admin tab > Server & Hardware > System Management > Configuration button in top-right corner > System Configuration page.)

**To add Grand Totals and Subtotals to rows:**

1. Click on the menu of the Row in the Data Panel, or right-click on the row header in the widget, and select
   Grand Totals or Subtotals.

   **Note:**

   Subtotals are only enabled if you have more than one row in your table.

   ![Pivot rows subtotal](https://docs.sisense.com/main/Resources/Images/pivot-rows-subtotal_474x239.png)
2. If you selected to display Subtotals, select the method by which to calculate them. Click the menu of the
   Value in the data panel, and then click **Subtotal by** and select the method.

   **Note:**

   You can choose different Subtotals in each field. To do this, click the header of the desired value in
   the table, click **Totals Calculation** and select the method.

   ![Pivot distinct totals](https://docs.sisense.com/main/Resources/Images/pivot-distinct-totals.png)

The default option is **Auto**, which aggregates all the data, sending a separate query to the
ElastiCube or data source. This means that Sisense applies the same formula set in the measure to the entire data.
The other custom options (Sum, Min, Max, etc.) calculate only the values in the rows above the Subtotal, based on
the data presented in the Pivot table, in place of the original formula set in the measure.

**Consider the following:**

![8 6sub totals eg4thumb](https://docs.sisense.com/main/Resources/Images/8-6sub-totals-eg4thumb0300.png)

In the top example, using the **Auto** option, the Subtotal for average revenue aggregates all the sales
and revenue data. This represents a true Subtotal of the average revenue for the Asia region.

In the bottom example, **Average** is used to calculate the Subtotal, and therefore calculates the
average of all the average revenue values in the rows above the Subtotal.

### Distinct Totals in Pivot Tables

The process of denormalization of the data sometimes creates duplicates. Duplicates are not a big problem when
processing text, because you can always "count distinct". However, they are a big problem when processing
numbers because you cannot "sum distinct".

There is a new Total calculation type - **Distinct Totals**. When enabled, duplicate values are
eliminated before the aggregation is calculated.

Analytical Engine calculates the totals, based on distinct values (eliminating any many-to-many impact, and avoiding
counting values multiple times.)

The administrator can set the **Enable Distinct Total Per Measure** default behavior in **Admin** > **System Management** > **Configuration** > **Pivot Table**.

The dashboard designer can set the required behavior for each pivot table.

**Example 1**

There are two tables in the database - FILMS and GENRES.

|  |
| --- |
| **FILMS** |
| **ID** | **DURATION** |
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |

|  |
| --- |
| **GENRES** |
| **FILM** | **GENRE** |
| 1 | Comedy |
| 1 | Horror |
| 2 | Drama |
| 3 | Comedy |
| 3 | Drama |

In the dashboard, a pivot table is created, based only on the FILMS table:

|  |
| --- |
| **FILM DURATION** |
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| TOTAL | 600 |

Another pivot table is created, based on the relationship between the FILMS and GENRES
tables, where:

- FILMS = aggregated table (parent)
- GENRES = attribute table (child)

|  |
| --- |
| **FILMS DURATION AND GENRE** |
| **GENRE** | **FILM ID** | **DURATION** |
| Comedy | 1 | 100 |
| Comedy | 3 | 300 |
| **Comedy Subtotal** | **400** |
| Drama | 2 | 200 |
| Drama | 3 | 300 |
| **Drama Subtotal** | **500** |
| Horror | 1 | 100 |
| **Horror Subtotal** | **100** |
| TOTAL | ?? |

Grand total duration calculation:

- Auto = 1000
- Manual Sum = 1000
- Distinct total = 600

Rows in the aggregated table that do not have a matching row in the attribute table are
not counted in the total calculation.

A measure from the aggregated table (FILMS) is NOT duplicated in the sum, regardless of
the number of times it appears in the lower granularity attribute table (GENRES).

**Example 2**

Consider the table below. The intention is to see the annual growth rate for 2010.

![VOsSrIqDlyd6s1kkCi5HUqn9dy9glvS42U7WHykTnAeiHS5wBhdE6qUF02JMY2HcFimjsqiDkN7ZhLfW9NVRK8ybrDIoUwSDYtTbbUVFVxiY29QO9Cd8wJrnfLxPRQBFGIpi9vCEbCdaAb68lA](https://docs.sisense.com/main/Resources/Images/vOsSrIqDlyd6s1kkCi5HUqn9dy9glvS42U7WHykTnAeiHS5wBhdE6qUF02JMY2HcFimjsqiDkN7ZhLfW9NVRK8ybrDIoUwSDYtTbbUVFVxiY29QO9Cd8wJrnfLxPRQBFGIpi9vCEbCdaAb68lA.png)

Distinct Totals returns 3.10% because it calculates the average for the entire period (the whole of 2010) against the
database itself and can therefore compare the ending number against the starting number.

Auto and Manual Totals return 38.17% because they add the monthly growth rates returned and do not have access to the
whole year's data.

![WX3qfYL6Fn7CUr6h7a2w5eZPGwFDBCb8Ua8 OVM7MZbxQg0iziW4HXF94XbtZgJH2o31lGN9cNTmnSTnctGtaJNo1Hd ZnWchZ5nVx922bVzvYt aDnUjRri1X3V0LiNwVnwjRCDQOFI ofg7w](https://docs.sisense.com/main/Resources/Images/wX3qfYL6Fn7CUr6h7a2w5eZPGwFDBCb8Ua8_OVM7MZbxQg0iziW4HXF94XbtZgJH2o31lGN9cNTmnSTnctGtaJNo1Hd_ZnWchZ5nVx922bVzvYt-aDnUjRri1X3V0LiNwVnwjRCDQOFI_ofg7w.png)

### Grand Totals

Grand totals aggregate the rows in the pivot table.

**To add grand totals:**

- Click on the menu of the row in the data panel, or right-click on the row header in the widget, and then
  click **Grand total**.

  If defined, the Grand Totals is affected by Subtotals. In the following example, the Grand Total for Average
  Sales is 10, representing the average sales for two regions (Asia and Europe). In the example below, Average
  Sales has a Subtotal (see above) set to Sum, and therefore the Grand total is the total of the rows above.

  ![8 6sub totals eg5thumb](https://docs.sisense.com/main/Resources/Images/8-6sub-totals-eg5thumb0300.png)

### Grand Total and Subtotal Calculations

In some cases, it may seem that the grand or subtotal are calculated incorrectly. The reason may be just the selected
calculation method. In other cases, you may want to calculate weighted averages in the grand and subtotals, instead
of the arithmetic mean that is used by the system.

**Grand Total and Subtotal Method Differences:**

- Change the **Subtotal By** from **Auto** to **Average**:

  ![8 6subtotals to averagethumb](https://docs.sisense.com/main/Resources/Images/8-6subtotals-to-averagethumb0300.png)

**To calculate weighted averages in the grand and Subtotals:**

- The subtotal by average takes the values in the rows above it, and therefore cannot be set to show a weighted
  (multi-pass) average. A way to achieve this is to change your formula in a way that when subtotals are set
  to auto, it will show the weighted average.

  For example, see the below use case (from the Sample Healthcare dashboard), using weighted aggregation:

  ![8 6subtotal weighted averagethumb](https://docs.sisense.com/main/Resources/Images/8-6subtotal-weighted-averagethumb0300.png)

  Two connected fields are used here: division.ID and Rooms.Division\_ID. The two fields are used from the
  dimension table in 'Rows' and its equivalent in the fact table in the formula.

In this example, we count beds per division, sum the results, and divide this result by the sum of rooms per
division. When looking at a specific division, it is the same as counting beds/rooms, but as a total, it is 10/59
(sum/sum) - the weighted average.

Click [here](https://support.sisense.com/hc/en-us/articles/360009144534-Calculating-Weighted-Average) for a detailed example.

## Sorting Pivot Tables

You can sort pivot tables:

- by rows alphabetically
- by values numerically
- by subtotals

Sorting pivot tables by multiple values enables flexibility for displaying data.

Sorting by values only applies to the last level of granularity. That is, sorting is applied to the groups within the
last row.

Note:

The sort order of exported CSV data will not necessarily match the exact sort order displayed in the Pivot widget.

### Sorting Alphabetically

Pivot table rows can be sorted alphabetically in ascending or descending order.

The following uses Example\_Pivot\_Table as the example.

**To sort Country alphabetically:**

1. Click ![Sort](https://docs.sisense.com/main/Resources/Images/sort_37x41.png) in the Country column to open the Sort - 'Country' menu.
2. Select Sort Ascending (A-Z) or Sort Descending (Z-A), as shown below.
     
   ![Image](https://docs.sisense.com/main/Resources/Images/image-1664781685715.png)

### Sorting by Values

Another option is sorting according to a column's values. Using Example\_Pivot\_Table as the example, if you wanted to
sort 'City' grouping by the 'Total Cost' values, you would go to the 'Total Cost' column and select 'City' to be
sorted by 'Total Cost'. You can also specify whether to sort in ascending or descending order.

**To sort City by Total Cost:**

1. Click
   ![Image](https://docs.sisense.com/main/Resources/Images/image-1664782021794_39x37.png)in the 'Total Cost' column to open the **Sort by 'Total Cost'** menu.
2. Select **City**, as shown below:  
   ![Image](https://docs.sisense.com/main/Resources/Images/image-1664785433349.png)
3. Select **Ascending (1-9)** or **Descending (9-1**).
4. Click **Apply.** 'City' is sorted by 'Total Cost' in the selected order numerically.

Note:

When adding new rows/values or reordering existing rows/values results in changing the position of sorted columns, the sorting is removed and must be re-added to respect the new Pivot structure. When adding the new rows/values does not change the position of sorted columns, the sorting rules remain as they were.

### Sorting According to Subtotals

All pivot rows, apart from the lowest level row, have 'Subtotals' (in the case of our example, it would be 'City').
Subtotals are optional (displayed by configuration) and display the aggregative value of that row level of
granularity.

The Example\_Pivot\_Table also shows the category details' subtotals, at the base of each row grouping, except 'Gender'
- which is the last level of granularity and has no need for subtotal data.

The subtotal for the 'Condition' row level, presents the aggregated value per column for all the 'Gender' values (as
if you had collapsed the pivot to not present 'Gender' level data).

![Image](https://docs.sisense.com/main/Resources/Images/image-1664800652580_727x569.png)

When choosing to sort by a row that is not the last level of granularity, you are actually choosing to sort by the
subtotal values. This can be in addition to sorting other levels of granularity, where each sort impacts the closest
level of details.

Sorting by values for rows that have subtotals is executed the same way as explained above.

**To sort Total Cost by subtotal:**

1. Click ![Image](https://docs.sisense.com/main/Resources/Images/image-1664786016544.png)in the **Total Cost**
   column to open the **Sort by 'Total Cost'** menu.
2. Select **Source (Subtotals)**, as shown below:  
   ![Image](https://docs.sisense.com/main/Resources/Images/image-1664786277881.png)
3. Select **Ascending (1-9)** or **Descending (9-1**).
4. Click **Apply.** The Total Cost row's subtotal is sorted in the selected order
   (ascending or descending) numerically.

## Embedding Images

**Note:**

For Linux systems starting with L2021.5, the best practice is to disable the embedded images plug-in and
use the following feature for embedded images. The embedded images plug-in will be deprecated in
starting with the L2021.7 release.

You can show images in the pivot table if there is a column with the images' URLs in the table's data
model.  
**To embed images:**

1. In the Rows panel, click **Add +** and select the field that contains the images' URL.
2. Click the field's menu and select **Show as Images**.  
   ![Embed image](https://docs.sisense.com/main/Resources/Images/embed-image.png)

If you store images under a separate domain from the one used for Sisense, you must configure the CORS setting so you can access the
image files. For more information, see [Cross Origin Resource Sharing](https://docs.sisense.com/main/SisenseLinux/cross-origin-resource-sharing.md).

**Note:**

In some instances, images will not appear in the widget's pdf output if the table holds many images and
there is a slow connection to the URL where the images are stored.

## Designing the Pivot Table

Fine-tune the appearance of the PIVOT table, using the following tools.

- **Colors**: Select the shading properties for the Pivot table's row and column elements.  
  ![8 6pivotcolorsthumb](https://docs.sisense.com/main/Resources/Images/8-6pivotcolorsthumb0300.png)
- **Manual Row Height**: Enable and enter a Row Height value (in pixels). If you are showing
  embedded images in your table, the images are resized to fill the row height and may be cropped because the
  column width is fixed.
- **Auto Height**: Automatically sets the Pivot table's widget height based on the content of a
  single page in the table.
- **Page Size**: Specify how many rows appear in each page. Paging options are provided
  accordingly. The maximum number of rows per page is 200 rows.

  **Note:**

  In Pivot tables with multiple pages, viewers can change the number of results displayed per page. This setting is not persistent, and resets to the default after the page refreshes.

### Exception Highlighting

Conditional formatting can be used for exception highlighting in a Pivot table. For example, as shown below:

![Pivot conditional](https://docs.sisense.com/main/Resources/Images/pivot-conditional.png)

See [Selecting Colors in Widgets](https://docs.sisense.com/main/SisenseLinux/selecting-colors-in-widgets.md) for more
information.

## Configuring Pivot Table Limits

To improve performance and limit resource usage, you can limit the amount of columns and rows displayed and define
the number of rows that can be queried by a Pivot Table widget.  
You access these settings as follows:

1. In Sisense, open the **Admin** page.
2. Search for and select **System Management** (located under Server & Hardware).
3. Click **Configuration**.![System management configuration](https://docs.sisense.com/main/Resources/Images/system-management-configuration.png)
4. Scroll down to the **Pivot Table** section and expand it.![Pivot table config](https://docs.sisense.com/main/Resources/Images/pivot-table-config_764x480.png)

- **Allow rendering Pivot Table content as HTML:** The default is to render HTML as plain text. When
  enabled, Sisense renders HTML as HTML. If you enable this option, click Save at the bottom of the page and click
  OK to apply the changes and restart the relevant Sisense services.
- **Maximum number of columns to display:** Limits the number of columns presented at the end of each
  page of the Pivot Table on the dashboard. The default number of columns is 70. Limiting the number of columns
  ensures that the number of cells on each page does not delay the rendering process for presenting a single pivot
  page.
- **Pivot Query LIMIT:** Enter any whole number larger than zero to set the maximum number of rows to
  include in a query results.

After you have configured these settings, scroll to the bottom of the System Configuration page and click
**Save**.

## Limitations

- Sisense supports the exporting of pivot tables of up to 1.5 million cells. Attempting to export a higher
  number of cells might result in a timeout. The below properties of a pivot table increase the probability of
  reaching a timeout, when dealing with very large tables:

  - Sub totals or Grand totals
  - Complex formulas in Values
  - Data Security rules
  - Widget-level filters
- To successfully export pivot tables, split large pivot table with many columns to separate smaller pivot
  tables.
- In the PDF Report Settings, the first 14 pages of your Pivot table are displayed in the preview window,
  however, when you export your Pivot table to PDF, the entire table is included, up to 1,000 rows on multiple
  pages.
- When sorting by values is applied to a Pivot widget with multiple rows and a single value per each row, the continuous running sum ("RSUM") is not recalculated according to the sorting order. For more information, see the RSUM function in [Dashboard Functions Reference](https://docs.sisense.com/main/SisenseLinux/dashboard-functions-reference.md#OtherFunctions).
- Manually added line break characters are visible in Sisense only when the Pivot is exported to CSV. They are not displayed in Pivot widgets or when exporting to Excel.

**Tip:**

For more information, see [Applying Conditional Formatting to Specific Columns in Sisense Pivot2](https://community.sisense.com/t5/knowledge-base/applying-conditional-formatting-to-specific-columns-in-sisense/ta-p/23387) on the Sisense Community site.