This dynamic and highly intuitive DAX-based eCommerce dashboard offers a real-time snapshot of the financial position of a business and facilitates future planning by highlighting metrics that directly impact the business's bottom line. When used as part of good business practices in a Financial Planning & Analysis (FP&A) department, financial dashboards improve executives' ability to always keep an eye on essential KPIs. This reduces the chances that important decisions are delayed because executives don’t have easy, self-service insight into performance. The dashboard is built to handle large datasets and provide insightful analytics for sales data spanning from 2019 to 2021, with a primary objective to facilitate better business decisions by visualizing key performance indicators such as cumulative sales, profitable customers, and profit trends.
The dataset used in this project is stored in a folder and comprises transaction details from 2019 to 2021. The data includes important information such as purchases, quantity, order dates, and identifiers that link to dimensional tables (Dim Tables). These Dim Tables contain data on customers, products, and other relevant dimensions, enabling comprehensive analysis through relationships created in Power Pivot.
- Data Extraction and Transformation: Utilized Power Query to extract and transform the data from the source files. The transformed data was then transferred to Power Pivot for further analysis.
- DAX Calculations: Leveraged DAX to define custom calculations in Power Pivot. This included using functions like
RELATED,SUMX, andSUMto generate reports on customer analysis and year-to-date profit trends. - KPI Calculation: Implemented functions such as
DISTINCTCOUNT,COUNTROWS, andIFto calculate the number of customers over time, the total number of transactions, and other key performance indicators (KPIs). - Automation: Automated the removal of manual filters using macros and VBA to streamline the data analysis process.
- Dashboard Creation: Designed an interactive dashboard using Power BI with filters for country, products, and months to enable dynamic data analysis.
-
Cumulative Profit: Visualizes the cumulative profit from the beginning of the transaction year to the last transactional year (2021) and updates dynamically with new data.
-
Top 3 Profitable Customers: Identifies the three most profitable customers, showcasing their contributions to company growth.
-
Top 3 Least Profitable Customers: Highlights the three least profitable customers, providing insights into potential areas for improvement and customer engagement strategies.
-
Yearly Profit and Growth: Displays yearly profit figures and growth percentages to track overall financial performance.
-
Monthly Profit Trend: Analyzes the trend of profits on a monthly basis to identify seasonal patterns and monthly performance fluctuations.
-
Quarterly Profit Trend: Examines profit trends on a quarterly basis for a broader view of financial performance across the year.
-
Active Filters: Includes interactive filters for country, products, and months. These filters allow users to quickly select options to consolidate data for one or multiple regions and adjust the analysis period. This enhances the dashboard's interactivity and flexibility.
-
Additional Insights: Visualizing critical KPIs with easy-to-read cards for smart and rapid decision-making, such as the number of transactions, total customers, current items, COGS, revenue, profit, quantity sold, and regions the company sells to.
- DAX (Data Analysis Expressions): Utilized for complex calculations and data manipulation within Excel.
- Power Query: Used for extracting and transforming data before analysis.
- Power Pivot: Used for creating relationships between Fact and Dim Tables, enabling seamless data integration and analysis.
- Excel: Employed for designing the interactive dashboard and visualizing key metrics and trends.
- VBA and Macros: Automated manual processes to improve efficiency.
- Analytical Thinking: Applied analytical skills to interpret data and derive meaningful insights.
- Attention to Detail: Ensured accuracy in data handling, calculations, and visualizations.
- Problem Solving: Identified and addressed challenges in data integration and dashboard design.
- Communication: Effectively communicated findings and insights through a well-structured dashboard.