Power BI Projects

Tarun Yadav Dec 23 2020 · 17 min read
Share this

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.

Customer Table
Product Table
Region Table
Sales Table
Date Table

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.

  • Reference Measures
  • Time Intelligence Measures
  • Year Calculation Measures
  • Refence Measures : This measures contain basic calculative measure life total cost , total price , total sales , total orders etc.   

    Reference Measure Table

    DAX Function for the above measure are listed below:

    DAX function for Profit Margin
    DAX function for Top 7 Customers
    DAX function for Top 7 Products
    DAX function for Total Cost
      DAX function for Total Order
    DAX function for Total Price
    DAX function for Total Profit
    DAX function for Total Quantity
    DAX function for Total Sales

    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:

    DAX function for Average Daily Sales
    DAX function for Profit LY
    DAX function for Profit Moving Average
    DAX function for Sales 2 years Ago
    DAX function for Sales LY
    DAX function for Sales Moving Average
    DAX function for Sales Variance

      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

    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.

    Student Survey Table 1.1
    Student Survey Table 1.2

    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:

    Condition Format option under visual format option
    Pop Up box after clicking advance control option under font color
    This is the result after doing above formatting on visual

    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 

    Got this as a result

    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

    Data Labels option under Format option
    Final result for that statement

    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 

    Filter panel for Visual Filtering
    Final result

    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 

    Bookmark and Selection panel for Indoor 
    Bookmark and Selection panel for Outdoor 
    Visual of Indoor after clicking indoor button
    Visual of Outdoor after clicking outdoor button

    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.

    Roles for Ashok so that he can only view Rural record 
    Roles for Mani so that she can only view Rural record 
    Roles for Nani so that he can only view Urban and Suburb record 
    Roles for Nitin so that he can only view Suburb record 

    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

    Dashboard Visual 1
    Dashboard Visual 2

    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.

    Q&A tab 1
    Visual with respect to Q&A answer
    Q&A tab 2 
    Visual with respect to Q&A answer

    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:

    Power BI Dashboard
    Power BI Report

    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.

    Employee table
    Customer table
    Product table
    Product Category table
      Product Sub-category table
    Product Promotional table
    Region 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:

    Splitting by delimiter box
    Splitting column after formatting

    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

    Reference Measure table
    Dax function for retail unit sold
    Dax function for sales unit sold
    Dax function total unit sold

    Specific Scenario Measure: This measure contain table consist of performance of product and sub-categories measure  

    Specific Scenarios Measure table
    Dax function performance of products
    Dax function performance of sub categories

      • Retails sales needs to have priority in terms of High, Mid & Low.

    Dax function for sales segment
    Formatted Column 

    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

    Visual Cards for total units sold retail unit sold and sales unit sold
    Pie chart for Unit sold by category

    • Unit sold in Promotion events in terms of reseller, no discount and Customers

    Tabular visual for the units sold in product promotion

    • Unit sold in terms Components, Accessories, clothing, Bikes

    Tabular visual for units sold by category

    • 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.

    Performance Matrix button
    Performance Matrix Report

    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:

    Comments
    Read next