Atliq a Multinational company wants to make data driven decision making as apart of their work culture and taking their first big step towards that by embedding a Power Bi tool in the organisation.
AtliQ Hardware is growing rapidly in the recent years, and they have decided to implement the data analytics using PowerBi in their company for the first time to surpass their competitors in the market and to make data driven decisions. This project is hoped to give answers to the questions of stakeholder in terms all the aspects like finance, sales, marketing and supply chain.
I worked on this project by following the Codebasics PowerBi Course, Link to the course is here
Executives, Finance, Supplychain, Sales & Marketing Teams
Unlock insights and enable data driven decisions
Care about it?
- SQL
- Power BI Desktop
- Excel
- DAX language
- DAX studio (for optimizing the report)
- What are all the questions should be asked before staring the project
- Creating calculated columns
- creating measure using DAX language
- Data modeling
- Using Bookmarks to switch between two visuals
- Page navigation with buttons
- Using divide function to prevent zero division errors
- creating date table using m language
- Dynamic titles based on the applied filters
- Using KPI indicators
- Conditional formatting the values in visuals using icons or background color
- Data validation techniques
- PowerBi services
- Publishing reports to PowerBi services
- Setting up personal gateway to set up the auto refresh of data
- PowerBi App creation
- Collaboration, workspace, access permissions in PowerBi services
- Finance
- Sales
- Marketing
- Supply Chain
- CALCULATE ( )
- DIVIDE ( )
- FILTER ( )
- SWITCH ( )
-
P and L statement : provides valuable insights into a company's profitability, financial viability, and overall performance, serving as a cornerstone for effective financial management and decision-making.
-
Gross price : Gross price refers to the total selling price of a product or service before any deductions Gross price = Net price
-
Pre-invoice deductions : Pre-invoice deductions refer to reductions or adjustments made to the total amount owed by a customer before the issuance of an invoice.(discounts,rebate,allowances)
-
Net Invoice sale : Gross Price - Pre-Invoice Deduction
-
Post-Invoice deductions :- it involves additional discounts like Promotional offers, placement fees , performance Rebate
-
Net sales : also known as revenue or net revenue, represents the total amount of sales generated by a company after deducting returns, discounts, and allowances Net Sales = Net Invoice sales - Post-Invoice deductions
-
COGS : stands for Cost of Goods Sold, which represents the direct costs associated with producing or acquiring the goods that a company sells during a specific period.
-
Gross Margin : It is a measure of profitability that represents the percentage of revenue left after subtracting the cost of goods sold (COGS). Gross Margin = Net sales - COGS = Gross Margin
-
Net Profit : or net income, is the amount of money a company retains after deducting all expenses from its total revenue. Profit = Revenue − Total Expenses( including COGS, operating expenses, taxes, interest, and other non-operating costs)
-
Gross Margin % = [(Revenue−COGS)/Revenue]×100
-
YTD - Year to Date refers to the period starting from the beginning of the current calendar year up to the present date.
-
YTG - Year to Go refers to the remaining portion of the current calendar year from the present date until the end of the year.
-
Direct : Refers to the direct sales channel where products are sold directly from the manufacturer or supplier to the end consumer without any intermediaries involved.
-
Retailer : Refers to businesses that purchase products from manufacturers or wholesalers and sell them to consumers through physical or online stores.
-
Distributors : Refers to intermediaries that purchase products in bulk from manufacturers and sell them to retailers or other businesses.
-
Consumer : You and me !
AltiQ hardware is a company which has grown vastly in the recent years, and opened business all over the globe. It is a company which sells, computer and computer accessories through three mediums/channel
- Retailers
- Direct
- Distributors
Recently the company has faced a unforeseen loss by opening store in America based on the surveys, intuition and some excel analysis and also the company’s competitors has handful of analytics team to perform analysis and make data driven decision. So, the AltiQ hardware has no other option other than building their analytics team for data driven insights and decisions in the future to survive better in the industry.
Project kick off session, where you should get clear of for what and why this project and all other questions you have with regards to the project
- What is the objective of building this PowerBi dashboard?
- In what terms the success of this project will be measured?
- What will be time dead-line of the project?
- do the stakeholders expecting pre-view before the actual release?
- What are all the hopes stakeholders have out of this project?
- what are all fears the stakeholder have in terms of building this dashboard?
- Who are all will be using this dashboard and for what purpose?
- what are all expectation the stakeholders have, by the completion of this project?
- What can go wrong while building this project?
- what are all the resources/ data needed to build this dashboard?
- is there any inputs from stakeholders in terms of design and views of the dashboard?
After the project kick off meetings, the data engineering team has given the data as per the request of data analytics team, let’s explore them.
- As the database is MySQL in this project, we need to import the datasets from Mysql database to PowerBi by providing the Database access credential
Based on the mock ups received as requirement, the team will start designing the visuals and create measure as and when required
In Home view, all the views button will be available. User will land on specific view page by clicking the button
-
Home page
-
Description
-
Finance View
-
Sales View
-
Marketing View
-
-
Supply chain View
-
-
Executive View
By using this report, decisions can be taken based on the data. Further it will help in answering n number of why questions based on the situations.