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-
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)
As evident from above visual, Total Profit comes out to be 57.79 millions with profit margin of 37%. Yearwise comes to be-
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]))
6.Sales 2yrs ago for any Selected Year
Sales_before_2years = CALCULATE([Totalsales],DATEADD('Date'[Date],-2,YEAR))
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.
8.A date table will make the analysis stable
We create a date table along with various columns using DAX functions.
9.Quarter wise analysis is also important, may be slicer.
To do the quarter wise analysis, we use a slicer-
10.Total Sales, Profit and % Profit can be shown as combo with card and line chart.
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]))
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
Project 2- Student Survey
Lets analyze our 'Student Survey' Project.
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.
We get output as below classified based on as per our requirement.
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.
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.
Here filter by 'Suburb'
5. a) Scatter plot - Video games purchase and 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 -
Similarly for Nani and Nitin
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.
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.
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 :)