Power BI Projects

##powerbi ##projects ##ineuron

Samruddhi Deshmukh Jan 01 2021 · 6 min read
Share this

Hello and welcome to my blog. Today we have projects categorized as  - Acquisition and  Student survey. We will discuss our approach in finding out solutions to the questions and visualize our end result in PBI desktop. Lets get started-

Acquisition Project

We will start with the first Project of 'Acquisition'. The project contains various details in the form of Customer Data , Product data, Regions,  and complete sales data in terms of order details, quantity, Total Cost, Total Revenue generated etc. Lets see our problem statement as mentioned below-

Optiveriance is planning to be merged with PDCL Ltd. Brown who is the CEO of PDCL is looking forward to a smooth transition and so after multiple rounds of discussion. They have decided to do audit of the business Infrastructure of Optiveriance. 

Justin who the Chief Data Officer along with his team started auditing on the following grounds:- 

1.What is the Total Sales?

To get information about Total sales, Profit, total quantity, or analyzing performance of any year, we use 'Calculated Measures' using DAX formula.

So what are Calculated measures?

-Calculated measures are virtual formulas used for any calculation. They are used to extract that information from the data which is otherwise  not available. Its a body of formula which will contain a value but they will not add any physical data to your column.

In the 'Sales' Table we have 'Total Revenue' for each order quantity.

So we find Total Sales using DAX formula as follows-

Total sales = sum(Sales[Total Revenue])

We get 'Total Sales' as 154.57 M.

2.What about the Total Quantity?

Total Quantity = SUM(Sales[Order Quantity]) 

We get 'Total Quantity' as 68K.

We use the 'Card' visual for 'Total Sales' and 'Total quantity'

3.Profit for the said period and the margin on the same.

Use DAX -    Total Profit = [Total sales]-[Total Cost]

Use DAX - % Profit Margin = DIVIDE([Total Profit],[Total sales],0)

We use a 'Card' visual for 'Total Profit' & '% Profit margin'. We use a 'Slicer' to see profit and profit margin for any desired year.

As evident from above visual, Total Profit comes out to be 57.79 millions with profit margin of 37%. Yearwise comes to be-

  • 2014- Profit of 13 million with a margin of 37%
  • 2015- Profit of 22.32 million with margin of 37.58%
  • 2016-Profit of 22.48 million with margin of 37.38%.
  • We see 2014 has least profit earned while profit for 2015 and 2016 is closer to each other.

    4.Need to have a details Performance LY for any selected Year.

    5.Comparing the Performance vs LY 

    We analyze the performance in terms of profit  last year. 

    Profit_lastyear = CALCULATE([TotalProfit],SAMEPERIODLASTYEAR('Date'[Date]))

    Performance in terms of 'Total Profit' for 2015 and last year of 2014.


    6.Sales 2yrs ago for any Selected Year

    Sales_before_2years = CALCULATE([Totalsales],DATEADD('Date'[Date],-2,YEAR))

    Total sales and Profit for 2016,2015 and 2014. Note current selected year is 2016 in the slicer.

    7. It is important to know the moving average in terms of Profit & Sales.

    We use DAX as follows to calculate moving avg Profit and sales.

    DAX for moving average in terms of profit (4 months)
    DAX for moving average in terms of sales (4 months)
    Moving average Profit and sales
    Moving average Profit and sales

    8.A date table will make the analysis stable

    We create a date table along with various columns using DAX functions.

    Date Table 

    9.Quarter wise analysis is also important, may be slicer.

    To do the quarter wise analysis, we use a slicer-

    Year wise slicer

    10.Total Sales, Profit and % Profit can be shown as combo with card and line chart. 

    Total sales and Profit by year shown by line chart and %Profit shown using card visual. 

    11.Show the sales Comparison between Cumulative Performance vs Cumulative Performance LY using a area chart.

    1.Cumulative Performance Profit = TOTALYTD([Total Profit],'Date'[Date])

    2.Cumulative Performance Profit LY = TOTALYTD([Total Profit], SAMEPERIODLASTYEAR('Date'[Date]))

    Cumulative Performance Profit comparison present year and LY

    12.Top Performer in terms of Product and Customer top 7 will give a clear idea. 

    To get the top 7 products and customers, we apply visual level filter and filter by top N

    Top 7 Product names, Top 7 Customers in terms of Revenue.

    Project 2- Student Survey

    Lets analyze  our 'Student Survey' Project.

    Problem statement-

    There are many stores in which a survey was conducted based on students i.e. how much they are spending on different kind 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. By using data set (Student Survey), try to extract the meaningful Insights. 

    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.

    -->We use conditional formatting from the visualization pane and apply on TAP column as per above stated conditions.

    Conditions on Total amount purchased (TAP) column through conditional formatting

    We get output as below classified based on as per our requirement.

    TAP based on ‘Store location’ and ‘Store setting'.

    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.

    Here we use the 'Matrix'  visual and used conditional formatting as discussed earlier to classify 'amount spend on outdoor sports' based on color from lowest to highest.

    Conditional formatting on Outdoor Sports
    Matrix visual  of 'Total amount spent on Outdoor sports' across different ages and store setting.

    3.Funnel chart – Create a Funnel chart to show Total amount of purchase by ‘Store setting’. Show the data labels as Percentage of First.

                                        %Amount of Purchased by Store Setting

    4. Pie chart – Show the total amount of purchase by different ‘Store location’ for Suburban ‘Store setting’ only. 

    Here filter by 'Suburb'

    Total amount of purchase by store location for Suburb

    5. a) Scatter plot - Video games purchase and Outdoor sports spent across the different ages.

    Video games purchase, Outdoor sports spent across the different 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. 

    Our requirement is to restrict as follows-

    We implement this by selecting the option "Manage Roles" in the Modelling section. We execute this as below - 

    Managing Roles for Ashok and Mani

    Similarly for Nani and Nitin

    Managing Roles for Managing Roles for Ashok and Mani

    Now we test the roles to see if they are working by selecting the "View as" option from the Modelling section. Let's view the data as Nani.

    Testing Role for Nani

    7. Publish the report on Power BI cloud service and Design the Master Dashboard consisting of Funnel chart and scatter plots. Then create a schedule refresh for six times in every 4 hours for the Dashboard in a day. 

    We publish the report to PowerBI Service by clicking "Publish" in the Home Section.

    Success message after report gets published on PBI service
    Reports as published on PBI service

    We schedule refresh as follows - 

    Install the Gateway connection and then  switch on the data gateway as follows- 

    Schedule the refresh as follows - 

    That was it for this project.

    Happy reading :)

    Read next