Steps Performed
Pivot tables played a critical role in structuring and organizing the dataset to support meaningful analysis and facilitate the creation of the dashboard. In this project, the pivot tables were designed to summarize key metrics across multiple dimensions—such as time periods (months and quarters), regions, and performance indicators. Below is a more detailed explanation of the pivot tables and how they were prepared.
1. Pivot for Monthly Sales and Targets
Purpose: To compare actual sales and target sales for each month, tracking progress and performance over time.
How it was prepared:
Rows: "Month" – This field was placed in the rows section to group the data by individual months.
Columns: “Sales (Units)” and “Target Sales” – These fields were placed in the columns section to show the actual sales figures compared to the target sales for each month.
Values:
"Sum of Sales (Units)" – Summing up the actual sales for each month.
"Sum of Target Sales" – Summing up the sales target for each month.
The result of this pivot provides a clear month-by-month comparison of actual sales versus target sales, allowing for easy performance tracking and variance analysis.
2. Pivot for Profit Analysis
Purpose: To analyze profit for each month and region, tracking whether the business is meeting its profitability targets.
How it was prepared:
Rows: "Month" and "Region" – The data is grouped by month, then further broken down by region to analyze profit performance on both a time and geographical basis.
Columns: "Profit (Amounts)" – This field shows the total profit in each region for every month.
Values:
"Sum of Profit (Amounts)" – Summing up the profit for each month and region.
This pivot table helps in understanding how profits vary by month and region, and it supports the identification of regions performing exceptionally well or poorly.
3. Pivot for Customer Analysis
Purpose: To analyze customer completion rates and numbers across months, regions, and sales performance.
How it was prepared:
Rows: "Month" and "Region" – The data is grouped by month and region to see how customer targets were met in each period and geographical area.
Columns: "Customers" – This field shows the total number of customers in each month and region.
Values:
"Sum of Customers" – Summing up the total number of customers per month and region.
"Average of Customer Completion Rate" – Calculating the average customer completion rate across the time periods, showing the effectiveness of customer acquisition and retention strategies.
This pivot table helps in understanding customer behavior and engagement trends, which are essential for evaluating overall business performance.
4. Pivot for Performance Analysis
Purpose: To track overall performance, assessing whether sales, profit, and customer engagement targets are being met.
How it was prepared:
Rows: "Month", "Quarter", and "Region" – This field setup allows the data to be broken down by both month and region, as well as aggregated by quarter to compare overall performance across different time scales.
Columns: "Performance" – This is a calculated field or metric based on sales completion, profit completion, and customer completion rates, which gives a holistic view of performance.
Values:
"Var of Performance" – Calculating the variance of performance from the target (whether the actual performance is above or below the target).
This pivot table was especially useful for aggregating performance data across regions and months, helping to quickly identify areas where performance deviates from expectations.
5. Pivot for Regional Sales and Profit Comparison
Purpose: To compare sales and profit performance across different regions, highlighting which regions are underperforming or overperforming.
How it was prepared:
Rows: "Region" – Grouping the data by region to compare performance across geographical areas.
Columns: "Sales (Units)" and "Profit (Amounts)" – Displaying the total sales and profit for each region.
Values:
"Sum of Sales (Units)" – Summing up the total units sold by region.
"Sum of Profit (Amounts)" – Summing up the total profit by region.
"Average of Profit Completion Rate" – Calculating the average completion rate for profits across regions to identify areas where profit targets are met or exceeded.
This pivot table is crucial for comparing the performance of different regions, allowing decision-makers to focus on underperforming areas for targeted improvements.
6. Pivot for Quarter-Based Performance Tracking
Purpose: To assess performance on a quarterly basis, helping to evaluate longer-term trends and seasonal variations.
How it was prepared:
Rows: "Quarter" – Grouping the data by quarter to analyze trends and performance over time.
Columns: "Performance" – A calculated metric based on the combination of sales, profit, and customer completion rates.
Values:
"Sum of Target Sales" – Summing the target sales across all months within each quarter.
"Sum of Sales (Units)" – Summing the actual sales for the quarter.
This pivot table helped to provide a broad overview of the business’s performance each quarter, offering insights into whether quarterly goals were met and identifying trends across the full year.
Once these pivot tables were set up and validated, they became the foundation for creating interactive charts and dashboards. They were linked to various graphical elements like bar charts, line charts, and area charts to make the data visually digestible. The pivots also facilitated the creation of filters that allowed users to interact with the dashboard and focus on specific time periods, regions, or performance metrics.
In summary, the pivot tables not only organized and summarized the raw data but also enabled the dynamic visual analysis that is central to the dashboard’s functionality. Each pivot was designed to answer a specific business question, providing the detailed breakdowns needed to support performance tracking and decision-making.