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:
Sumto 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!
.jpg)
