The Basic Key that i have followed in solving problem statement is to break down the whole statement into sub-parts though which it will get easier to solve the problem as whole.
Project - (Acquisition)
Problem Statement :
Optiveriance is planning to be merged with PDLC LTD. Brown is the CEO of PDLC and looking for a smooth transition and so after multiple rounds of discussion they have to decide to do audit of the business infrastructure of Optiveriance.
I have used PowerBi as a Data Analytic tool for the problem statement , the techniques and approach i have to show case the insight from the data to auditing on the following ground: • What is the Total Sales
• What about the Total Quantity
• Profit for the said period and the margin on the same.
• Comparing the Performance vs LY
• Sales 2yrs ago for any Selected Year
• It is important to know the moving average in terms of Profit & Sales.
• Quarter wise analysis is also important
• Top Performer in terms of Product and Customer top 7 will give a clear idea.
• Performance in terms of region sales needs to workout
# Note :
The best practice to a Data Analytics according to me is to follow the Data Analytics Project Cycle i.e 1. Understanding Data or Business
2. Collecting and Cleaning Data (Data preparation)
3. Model Planning and Model Building
4. Validation and Visualization
5. Deploying
Understanding Data :
In the data i have provided a Customer table, Product table, Region table and Sales table.
Customer table contains the data of the unique customers who order a product from the company, Product table contains the data of the unique products sold by the company, Region country table contains the information of the region of customer and finally Sales tables contain all the data when a particular product is ordered, from where it ordered, by whom is ordered and all the primary information like cost, price, profit etc.
Data Preparation :
There was no problem in data preparation part as the Data was very cleaned and up to mark there is no need of cleaning and removing anomalies form the data
Model Planning :
It is one of the most important part of whole Data Analytics Project Cycle because this is the stage in which we find the way to tackle the problem to find out results form the data and get desired output. I decide to create Measures using DAX function to find out the results of the problem statements.
Model Building :
I create three groups of Measure to complete the all the objectives of the problem statement.
Refence Measures : This measures contain basic calculative measure life total cost , total price , total sales , total orders etc.
DAX Function for the above measure are listed below:
2. Time Intelligence Measures : This measures contain all the time related calculations such as sales LY, profit LY, Moving Average for sales and profit etc.
DAX Function for the above measure are listed below:
3.Year Calculation Measures : This measures contain all the year related calculations such as Profit in 2014, 2015, 2016 and Profit Margin 2014, 2015, 2016 .
DAX Function for the above measure are listed below:
Final Model View Of all fact table , dimension table and Measure Table is attached below:
Visualization :
The Main goal is to make all the Visual Dynamic to create a more interaction between user and dashboard.
1. For the Visualization of Total Sales, Total Quantity and Total Profit is show through a Visual Card with a slicer to make it dynamic so that we can see all the information with respective years.
2. For the Visualization of Quarterly Sales i came up the the concept of drill-through and then after drilling through main page to quarterly sales is shown with a bar graph and a slicer for different years.
3. For the Visualization of Profit Analysis i have created a button which will paginate on click , used line and area chart for profit margin and profit of respective years.
4. For the Visualization of Moving Average i also created a button which will also paginate on click , created a dynamic line chart for moving average and moving average interval can be seen from slicer.
5. For the Visualization of Top Performer and LY performance i have create a button which will paginate on click, create a bookmark as button to see top 7 performer and LY performance.
6. For the Visualization of Region sales i have used word cloud visual bigger the size of word bigger the sales number.
Deploying :
Later the report is deployed on PowerBi Service.
Conclusion :
Finally a full report is developed created on Power Bi Desktop and deployed on Power BI service with all the requirement asked in the problem statement for the stake holders and end users.
Full Report is shown below:Pro
Project - (Student Spend Analysis)
Problem Statement:
I have used PowerBi as a Data Analytic tool for the problem statement , the techniques and approach i have used to show case the insight from the data to auditing on the following ground:
Create a Power BI Report:
1. Tabular Visualization - Format the total amount of
purchase (TAP) based on ‘Store location’ and ‘Store
setting’: -
If 0<TAP<35000, then records should be in red color
If 35000<=TAP<60000, then records should be in yellow
color
If TAP>=60000, then records should be in Blue color
2 Matrix Visualization – Create Matrix Visualization to show
the amount spent on Outdoor sports across
different ages and ‘Store setting’. Do the color formatting for
the amount spent in total outdoor sports.
3. Funnel chart – Create a Funnel chart to show Total amount
of purchase by ‘Store setting’. Show the
data labels as Percentage of First.
4. Pie chart – Show the total amount of purchase by different
‘Store location’ for Suburban ‘Store setting’
only. Hint: Use Filter context
5. a) Scatter plot - Video games purchase and Outdoor sports
spent across the different ages.
b) Sand dance plot - Indoor sports and Video games spent
across the different age groups.
6. Restrict data access for the given users in User mapping
table. For ex. Mani deals with Rural area only
so she should be able to view the data which belongs to Rural
only, not urban and suburban data.
7. Publish the report on Power BI cloud service and Design
the Master Dashboard consisting of Funnel
chart and scatter plots.
8. Use Q&A feature of Power BI –
a) To show average age of students
b) Donut chart for total amount of purchases by ‘Store
location’
# Note :
The best practice to a Data Analytics according to me is to follow the Data Analytics Project Cycle i.e 1. Understanding Data or Business
2. Collecting and Cleaning Data (Data preparation)
3. Model Planning and Model Building
4. Validation and Visualization
5. Deploying
Understanding Data :
The data was about, How much they are spending on different kinds of purchases like video games, indoor games, toys, books, gadgets etc. In the data set (Student Survey) , Store setting is the column that explains the Type of location in which the store is present, Store setting is the column which explain the region type of a store and other important column like total amount of purchase from students and purchase column like outdoor, indoor etc.
Data Preparation :
There was no problem in data preparation part as the Data was very cleaned and up to mark there is no need of cleaning and removing anomalies form the data.
Model Planning & Model Building :
It is one of the most important part of whole Data Analytics Project Cycle because this is the stage in which we find the way to tackle the problem to find out results form the data and get desired output. As there was nothing much requirement of using DAX function in according to the problem statement.
Visualization :
1. Tabular Visualization - Format the total amount of
purchase (TAP) based on ‘Store location’ and ‘Store
setting’: -
If 0<TAP<35000, then records should be in red color
If 35000<=TAP<60000, then records should be in yellow
color
If TAP>=60000, then records should be in Blue color
For this problem i decide to go with the table visualization but there i one conditional formatting, to accomplish this i used in build condition formatting in visual panel only under format option and then click on advance option to do custom formatting as shown below:
2 Matrix Visualization – Create Matrix Visualization to show
the amount spent on Outdoor sports across
different ages and ‘Store setting’. Do the color formatting for
the amount spent in total outdoor sports.
For this statement there is a matrix visual in visual panel and then choose Age as rows , Store setting as column and Outdoor Sports kit as values
3. Funnel chart – Create a Funnel chart to show Total amount
of purchase by ‘Store setting’. Show the
data labels as Percentage of First.
For this statement we have funnel chart by default in visual panel but there is a formatting we have to do in this visual according to the statement which is also can be find easily in the Data Labels option under Format option of visual
4. Pie chart – Show the total amount of purchase by different
‘Store location’ for Suburban ‘Store setting’
only. Hint: Use Filter context
For this statement there is a default visual under visual panel and again we have to do some formatting and to accomplish this we have to use Store Setting as Visual Filters and the select Suburb
5. a) Scatter plot - Video games purchase and Outdoor sports
spent across the different ages.
b) Sand dance plot - Indoor sports and Video games spent
across the different age groups.
For this problem i decided to got with the one of the report toggling method i.e Bookmarks and used Button to toggle between outdoor and indoor total purchase by ages
6. Restrict data access for the given users in User mapping
table. For ex. Mani deals with Rural area only
so she should be able to view the data which belongs to Rural
only, not urban and suburban data.
For this task i have to create roles for each store setting according to the UserID from User mapping table. It can be done by Managing Roles under format option in menu ribbon of power bi desktop and assign the respective roles.
7. Publish the report on Power BI cloud service and Design
the Master Dashboard consisting of Funnel
chart and scatter plots.
For this problem i published report on Power Bi Service and create a master dashboard and Funnel char , Scatter plots are pinned to dashboard
8. Use Q&A feature of Power BI –
a) To show average age of students
b) Donut chart for total amount of purchases by ‘Store
location’
After publishing a report on Power Bi Service there is a option of Q/A in edit option in which we can ask question from out report and it will try to answer it through using NLP(Natural Language Processes) techniques and later we can convert its answer into visual and pinned it to dashboard or save it in report.
Conclusion :
Finally a full report is developed created on Power Bi Desktop and deployed on Power BI service with all the requirement asked in the problem statement for the stake holders and end users.
Full Report is shown below:
Project - (Product Analysis)
Problem Statement :
Essies Ltd is launching a new product and in the initial stage they are targeting some segmented customer and region. You are the sales head has just got the new details from the frontline retailers. He has appointed Benson to infer some important insights
Benson starts the execution after he understands the business with the following:-
• The data requires some reference table to be created which can be used for using the date and products.
• It is important that the product Category table is kept separate in the model.
• Benson decides to keep the Product Sub category table to be kept as separate table.
• Retails sales needs to have priority in terms of High, Mid & Low.
• You needs to have Interactive slicer in terms of Country Flag in the report
• Essies Dashboard needs to highlight the following:-
o Units Sold by Category
o Total Unit sold
o Retail Unit Sold
o Sales Unit Sold
• Top performing products in terms of Unit sold
• Top performing in subcategories in terms of Unit sold
Marketing head John has also carried a big campaign to penetrate the inclusive and he is also keen to know the following details:-
• Unit sold in Promotion events in terms of reseller, no discount and Customers
• Unit sold in terms Components, Accessories, clothing, Bikes
# Note :
The best practice to a Data Analytics according to me is to follow the Data Analytics Project Cycle i.e 1. Understanding Data or Business
2. Collecting and Cleaning Data (Data preparation)
3. Model Planning and Model Building
4. Validation and Visualization
5. Deploying
Understanding Data :
Data was about the product sales details , retail sales, region wise sales, promotion with respect to different types of sales etc.
Data composed of Employee detail table, Customer detail table, Product Table, Product Category table, Product Sub-category table, Retail sales table, Sales detail table.
Data Preparation :
There was a bit problem in data other than that Data was very cleaned and up to mark there is no need of cleaning and removing anomalies form the data. The problem was in Retail Sales and Sales detail table in which measures column are in string format which is needed to be number format for data modeling part which is handled in PQE(Power Query Editor) by splitting column by delimiter as shown in pictures below:
Model Planning :
It is one of the most important part of whole Data Analytics Project Cycle because this is the stage in which we find the way to tackle the problem to find out results form the data and get desired output. I decide to create Measures using DAX function to find out the results of the problem statements.
Model Building :
I create three groups of Measure to complete the all the objectives of the problem statement.
Reference Measure: This measure table consist of a basic calculative measure like total unit sold, retail unit sold and total unit sold
Specific Scenario Measure: This measure contain table consist of performance of product and sub-categories measure
• Retails sales needs to have priority in terms of High, Mid & Low.
Visualization :
• You needs to have Interactive slicer in terms of Country Flag in the report
• Essies Dashboard needs to highlight the following:-
o Units Sold by Category
o Total Unit sold
o Retail Unit Sold
o Sales Unit Sold
• Unit sold in Promotion events in terms of reseller, no discount and Customers
• Unit sold in terms Components, Accessories, clothing, Bikes
• Top performing products in terms of Unit sold
• Top performing in subcategories in terms of Unit sold
For the visual of above , decided to create a button for Performance Matrix which will on click page navigation to performance matrix report in which i have used a bar chart for Top 10 Performing Product on the bases of unit sold, also used Tree chart for Top 10 Performing Sub categories and used a donut chart for total unit sold by countries.
Deploying :
Later the report is deployed on PowerBi Service.
Conclusion :
Finally a full report is developed created on Power Bi Desktop and deployed on Power BI service with all the requirement asked in the problem statement for the stake holders and end users.
Full Report is shown below:
