cancel
Showing results for 
Search instead for 
Did you mean: 
JoeM
Community Manager
Community Manager

Pivot tables are no stranger to the world of BI. In this article, we make some recommendations and key considerations for when you build your next pivot tables.

What you should know before reading this article

How to Create a Pivot Table

While there is an apparent insight type of pivot, there is another way to flip a table so rows become columns and columns become rows. This can be done by going to the cog (settings) in the analyzer in the upper right. Within the settings, there is a toggle to transpose the table. 

JoeM_0-1653672618825.jpeg  

We recommend developing the insight as a pivot table before leveraging the 'transpose' capability. Due to the setting's hidden nature, building a pivot table from the outset will help other insight developers quickly key in on how the insight was constructed.

Because pivot tables are typically a form of an aggregated table, it is paramount to provide your insight consumer with access to the underlying detail data. If a user sees an unexpected value, give the user the ability to drill into the data and confirm or deny their suspicion.

As with aggregated tables, ensure that data is cleansed and will provide accurate aggregation. For example, something as simple as casing differences ('Product A' vs. 'Product a') between the same dimension value can cause data to group into different rows.

The default behavior of Incorta is to fill null rows with blank space. In many cases, you might seek to impute a value of 0. To do so, go to the analyzer settings, scroll to the 'Advanced' section and enter a 'Missing Text Value.'

JoeM_1-1653672618833.jpeg

Enhance the legibility of your report using totals or subtotals.

Totals: In the analyzer insight settings (top-right cog), select whether you would like to add a grand total column or a grand total row. You also have options to right-align or left-align totals. Traditional preference dictates that totals sit on the right side or bottom of the pivot chart.

Subtotals: In the row pill properties, toggle on the subtotals settings. In the pictures below, subtotals for the 'Quarter' pill are toggled on!

Before

JoeM_2-1653672618909.jpeg

 

 After

JoeM_3-1653672619079.jpeg

 

 

Before (Defaulted to sorting by Category):

JoeM_4-1653672618951.jpeg

 After (Sorted by Linetotal):

JoeM_5-1653672618928.jpeg

By default, the merge rows configuration should be toggled on. Good! If not, go to the insight settings (top right cog) and toggle 'merge rows' on.

Before

JoeM_6-1653672619051.jpeg

 

After

JoeM_7-1653672619097.jpeg

 

 

Contributors
Version history
Last update:
‎05-27-2022 10:37 AM
Updated by: