Creating Pivot Tables in Apache Superset

  

In this post, we’ll move beyond basic charts and explore one of the most powerful analytical tools in Apache Superset, Pivot Tables.

 

To make things more practical and relatable, we’ll work with a real-world inspired e-commerce dataset that includes regions, product categories, payment methods, order statuses, and revenue. This kind of data is very common in modern businesses and is perfect for understanding how different dimensions interact with each other.

 

Imagine you’re an analyst trying to answer questions like:

 

·      Which product categories generate the highest revenue in each region?

·      Do certain payment methods lead to more cancellations or returns?

·      Which regions are consistently performing well across multiple days?

 

Instead of writing complex queries or scanning large tables, pivot tables allow you to summarize, compare, and analyze data across multiple dimensions in a single view.

 

In this post, we’ll build a pivot table step by step using this dataset and learn how to:

 

·      Organize data into rows and columns for cross-tab analysis

·      Apply aggregations like total revenue and total orders

·      Enable totals and subtotals for better insights

·      Use conditional formatting to instantly highlight high and low performance

 

By the end, you’ll be able to transform raw transactional data into a clear, structured, and insight driven report using Apache Superset.

 

Sample Dataset

commerce_orders.csv

order_date,region,product_category,payment_method,order_status,orders,revenue
2026-04-01,North,Electronics,UPI,Completed,120,240000
2026-04-01,North,Clothing,Credit Card,Completed,80,120000
2026-04-01,South,Clothing,Credit Card,Completed,90,135000
2026-04-01,South,Groceries,UPI,Completed,110,55000
2026-04-01,West,Groceries,Cash on Delivery,Completed,150,75000
2026-04-01,West,Electronics,Debit Card,Completed,70,210000
2026-04-01,East,Electronics,Debit Card,Cancelled,20,40000
2026-04-01,East,Clothing,UPI,Returned,30,45000
2026-04-01,Central,Groceries,Cash on Delivery,Completed,100,50000
2026-04-02,North,Clothing,UPI,Completed,110,165000
2026-04-02,North,Groceries,Debit Card,Completed,95,47500
2026-04-02,South,Electronics,Credit Card,Completed,95,285000
2026-04-02,South,Clothing,UPI,Completed,85,127500
2026-04-02,West,Groceries,Cash on Delivery,Completed,140,70000
2026-04-02,West,Clothing,Debit Card,Completed,100,150000
2026-04-02,East,Clothing,Debit Card,Returned,25,37500
2026-04-02,East,Electronics,UPI,Completed,60,180000
2026-04-02,Central,Electronics,Credit Card,Completed,75,225000
2026-04-03,North,Groceries,UPI,Completed,130,65000
2026-04-03,North,Electronics,Credit Card,Completed,90,270000
2026-04-03,South,Electronics,Credit Card,Completed,100,300000
2026-04-03,South,Groceries,Cash on Delivery,Completed,120,60000
2026-04-03,West,Clothing,Cash on Delivery,Completed,120,180000
2026-04-03,West,Electronics,UPI,Completed,80,240000
2026-04-03,East,Electronics,Debit Card,Completed,80,240000
2026-04-03,East,Clothing,UPI,Completed,60,90000
2026-04-03,Central,Groceries,Credit Card,Completed,110,55000
2026-04-03,Central,Clothing,Debit Card,Returned,20,30000
2026-04-04,North,Electronics,UPI,Returned,30,90000
2026-04-04,North,Clothing,Credit Card,Completed,85,127500
2026-04-04,South,Clothing,Credit Card,Completed,85,127500
2026-04-04,South,Groceries,UPI,Completed,115,57500
2026-04-04,West,Groceries,Cash on Delivery,Completed,160,80000
2026-04-04,West,Electronics,Debit Card,Completed,75,225000
2026-04-04,East,Clothing,Debit Card,Completed,70,105000
2026-04-04,East,Electronics,UPI,Completed,65,195000
2026-04-04,Central,Groceries,Credit Card,Completed,120,60000
2026-04-05,North,Electronics,UPI,Completed,140,280000
2026-04-05,North,Groceries,Debit Card,Completed,100,50000
2026-04-05,South,Groceries,Credit Card,Completed,110,55000
2026-04-05,South,Clothing,UPI,Completed,90,135000
2026-04-05,West,Clothing,Cash on Delivery,Completed,130,195000
2026-04-05,West,Electronics,Debit Card,Completed,85,255000
2026-04-05,East,Electronics,Debit Card,Completed,90,270000
2026-04-05,East,Groceries,UPI,Completed,105,52500
2026-04-05,Central,Clothing,Credit Card,Completed,95,142500
2026-04-06,North,Clothing,UPI,Completed,115,172500
2026-04-06,North,Electronics,Credit Card,Completed,95,285000
2026-04-06,South,Electronics,Credit Card,Cancelled,40,120000
2026-04-06,South,Groceries,UPI,Completed,120,60000
2026-04-06,West,Groceries,Cash on Delivery,Completed,150,75000
2026-04-06,West,Clothing,Debit Card,Completed,110,165000
2026-04-06,East,Clothing,Debit Card,Completed,85,127500
2026-04-06,East,Electronics,UPI,Completed,70,210000
2026-04-06,Central,Electronics,Credit Card,Completed,80,240000
2026-04-07,North,Groceries,UPI,Completed,135,67500
2026-04-07,North,Electronics,Credit Card,Completed,100,300000
2026-04-07,South,Electronics,Credit Card,Completed,105,315000
2026-04-07,South,Clothing,UPI,Completed,95,142500
2026-04-07,West,Clothing,Cash on Delivery,Returned,35,52500
2026-04-07,West,Groceries,Debit Card,Completed,145,72500
2026-04-07,East,Electronics,Debit Card,Completed,95,285000
2026-04-07,East,Groceries,UPI,Completed,110,55000
2026-04-07,Central,Clothing,Credit Card,Completed,100,150000
2026-04-08,North,Electronics,UPI,Completed,150,300000
2026-04-08,North,Clothing,Credit Card,Completed,105,157500
2026-04-08,South,Clothing,Credit Card,Completed,100,150000
2026-04-08,South,Groceries,UPI,Completed,125,62500
2026-04-08,West,Groceries,Cash on Delivery,Completed,160,80000
2026-04-08,West,Electronics,Debit Card,Completed,90,270000
2026-04-08,East,Clothing,Debit Card,Cancelled,30,45000
2026-04-08,East,Electronics,UPI,Completed,75,225000
2026-04-08,Central,Groceries,Credit Card,Completed,130,65000
2026-04-09,North,Clothing,UPI,Completed,120,180000
2026-04-09,North,Electronics,Credit Card,Completed,110,330000
2026-04-09,South,Electronics,Credit Card,Completed,110,330000
2026-04-09,South,Groceries,UPI,Completed,130,65000
2026-04-09,West,Groceries,Cash on Delivery,Completed,155,77500
2026-04-09,West,Clothing,Debit Card,Completed,120,180000
2026-04-09,East,Electronics,Debit Card,Completed,100,300000
2026-04-09,East,Groceries,UPI,Completed,115,57500
2026-04-09,Central,Electronics,Credit Card,Completed,105,315000
2026-04-10,North,Groceries,UPI,Completed,140,70000
2026-04-10,North,Electronics,Credit Card,Completed,115,345000
2026-04-10,South,Clothing,Credit Card,Completed,95,142500
2026-04-10,South,Electronics,UPI,Completed,120,360000
2026-04-10,West,Electronics,Cash on Delivery,Completed,120,360000
2026-04-10,West,Groceries,Debit Card,Completed,160,80000
2026-04-10,East,Clothing,Debit Card,Returned,40,60000
2026-04-10,East,Electronics,UPI,Completed,85,255000
2026-04-10,Central,Clothing,Credit Card,Completed,110,165000

   

Before we jump into building the pivot table, let’s take a moment to understand the dataset we’ll be working with.

 

This dataset represents a simplified e-commerce order system, capturing how different regions, product categories, and payment methods contribute to overall business performance over time. Each row in the dataset represents aggregated daily activity for a specific combination of dimensions.

 

·      order_date: This represents the date of the transaction. It helps us analyze trends over time and compare daily performance.

·      region: Indicates the geographical area where the order was placed (North, South, East, West). This is useful for understanding regional performance and identifying strong or weak markets.

·      product_category: Represents the type of product sold, such as Electronics, Clothing, and Groceries.

·      This helps us analyze which categories are driving revenue.

·      payment_method: Shows how customers paid for their orders (UPI, Credit Card, Debit Card, Cash on Delivery).

·      This is especially useful for identifying customer preferences and potential issues like higher cancellations in certain payment types.

·      order_status: Indicates the outcome of the order like Completed, Cancelled and Returned

·      orders: Represents the total number of orders for that combination of dimensions on a given day.

·      revenue: Represents the total revenue generated from those orders.

 

This dataset is intentionally designed to support multi-dimensional analysis, which is exactly what pivot tables excel at.

 

Now follow below step-by-step procedure to build the Pivot table using above data.

 

Step 1: Upload Commerce orders csv data to Superset.

 

Data -> Upload CSV to database

 

·      Upload commerce_orders.csv file

·      Set the database and schema name where you want to upload the csv file

·      Set the table name as commerce_orders

 

Expands Columns section.

 

·      Select all columns for Columns to read section.

·      Set Column data types to {"order_date": "datetime64[ns]", "region": "string", "product_category" : "string", "payment_method" : "string", "order_status" : "string", "orders" : "int32", "revenue" : "float32"}

 

Click on Upload button to upload the dataset.

 

Navigate to datasets listing page, you can able to see commerce_orders dataset.

 

Step 2: Create Pivot table

Click on commerce_orders dataset, it takes you to the Chart configuration page.

 

Click on ‘View all charts’ link.

 

Select Pivot table.

 

In the Columns section, we define what will appear as the column headers at the top of the table. Drag the region column into this section.

 

Next, in the Rows section, we define what will appear as row headers along the left side of the table. Drag the product_category column into this section.

 

Then, drag the payment_method column into the Rows as well. This will create separate rows for each payment method.

 

In the Metrics section, we define the values that will populate the cells of the pivot table. Here, use revenue as the metric—drag and drop it into the Metrics section and set the aggregation to SUM. This will calclate total revenue for each category and payment method.

 

Save the changes.

 

Click on Create chart button. It displays Pivot table like below.

 

Experiment with Aggregation function

Options -> select Aggregation function as Sum.

 

Select ‘show rows total’ checkbox.

 

Table is added with another new column Total(sum) that calculates entire row sum.

 

Select ‘Show rows subtotal’ option. You can observe Subtotal is calculated for each category and region.  

 

Select ‘Show columns total’, it shows total column sum.

 

Select ‘Transpose Pivot’. It flips your Pivot table, swap rows and columns. This is useful when you want to change the perspective of your analysis.

 

Let me uncheck all options before next experiments.

 

How to apply Conditional formatting to cells?

Conditional formatting helps to highlight important patterns in our data.

 

Navigate to Customize tab, click on + button below Conditional formatting section.

 

Set Error color scheme where SUM(revenue) is less than or equal to zero.

 

Update the chart.

 

Conditional formatting elevates your pivot table from a basic data display to a powerful analytical tool, instantly highlighting key patterns and insights.

  

Previous                                                    Next                                                    Home