How to Use a Pivot Table In a Dashboard View

Welcome to another tutorial on our website. Today we'll walk through how to use a pivot table in a dashboard view. This is a new feature, available as a widget in dashboard views. Let's dive in!

Setting Up the Tables

We'll quickly walk through the solution. There are three tables:

• Accounts Table: A list of accounts, from account 1 to account 21.
• Products Table: A list of products, from product 1 to product 30, each with unique price points per unit.
• Sales Table: Acts as a junction table linking accounts to products and recording the date of sale, quantity sold, and the total cost for each sale.

Accounts Table

This table has a list of accounts:

| Account ID | Account Name ||------------|--------------|| 1          | Account 1    || 2          | Account 2    || ...        | ...          || 21         | Account 21   |

Products Table

This table contains products and their price points:

| Product ID | Product Name | Price per Unit ||------------|--------------|----------------|| 1          | Product 1    | $10            || 2          | Product 2    | $15            || ...        | ...          | ...            || 30         | Product 30   | $50

Sales Table

The Sales table links products to accounts and records sale data:

| Sale ID | Date       | Account ID | Product ID | Quantity | Total Cost ||---------|------------|------------|------------|----------|------------|| 1       | 2023-10-01 | 1          | 5          | 10       | $150       || 2       | 2023-10-02 | 2          | 8          | 5        | $200       || ...     | ...        | ...        | ...        | ...      | ...

It focuses on the date, quantity sold, and a formula to calculate total cost.

Creating the Pivot Table

Now let's look at the pivot table in a dashboard view.

Opening the Dashboard View

Pivot tables are available in the Smart Suite Views section of the widget library. Select the Pivot Table option.

The configuration settings menu allows you to:

• Title: Name the pivot table
• Description: Add a description
• Solution: Choose the related solution

Setup the Pivot Table

Let's use the sales table and determine rows and columns:

Setting Rows and Columns

• Rows: Select accounts to see a list of accounts
• Columns: Select products to see what was sold

Use ascending order for better readability:

• Accounts: 1, 2, 3,...
• Products: 1, 2, 3,...

Summary and Assessment

We want to assess the total cost, not just count the sales.

Determine Summary Options

• Field to Assess: Total cost field
• Summary Type: Sum to total the cost

There are other summary options like max, average, etc. But here we'll sum the total cost of products sold by each account.

"This dot notation allows us to dive into the linked record and extract a specific field value from the record that's linked. And you are seeing that automated output here via the formula for how much this sale was worth."

Conclusion

Utilizing pivot tables in a dashboard provides a powerful way to analyze your data visually.

To experience the future of work management firsthand, start your free SmartSuite trial today!