PowerBI Projects

##powerbi ##projects ##dax ##realtimedatasets #powerbisecurity

Urvin Desai Dec 25 2020 · 11 min read
Share this

Hello Everyone, my name is Urvin Desai. I will be describing the approach used for implementing the projects. There are a total of 3 projects related to company acquisition, student survey and product development respectively. I will go through each of them serially.

Company Acquisition

Let's discuss the dataset first. We have sales data corresponding to different regions, order dates, customers, etc. Let's see the snapshot of it - 

Sales Data

We then have the customer, region, and product data. 

Let's jump to the requirements for the tasks.

The problem statement is as follows - 

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:-

What is Total Sales?

We use the below DAX formula for this - 

Total Sales = SUM(Sales[Total Revenue])

Then we use CARDS visual to show the value which is as follows - 

Total Sales

What is the Total Quantity?

We use the below DAX formula for this - 

Total Quantity = SUM(Sales[Order Quantity])

The total quantity is as follows - 

Total Quantity

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

We use the following DAX formulas respectively - 

Profit = [Total Sales] - SUMX(Sales, Sales[Total Unit Cost] * Sales[Order Quantity])
Profit Margin = ([Total Sales] - [Total Cost]) / [Total Sales]

We have a total profit as follows - 

Total Profit

We can visualize the profit for the said period by selecting the year value from the slicer - 

Year Selection Slicer

The total profit margin is as follows - 

Total Profit Margin

We can check the profit margin for the said period by selecting the year value from the slicer.

Need to have a detailed Performance LY for any selected Year.

We use the following DAX formula for this - 

Performance LY = CALCULATE([Profit], PREVIOUSYEAR('Date Table'[Date]))

We get the following result if we select 2016 as the year - 

Performance for the 2015 year(Last Year for 2016)

Comparing the Performance vs LY.

We use the below DAX formula for finding Performance LY - 

Performance LY = CALCULATE([Profit], DATEADD('Date Table'[Date], -1, YEAR))

We get the following output as a comparison for the 2016 year - 

Performance vs Performance LY

Sales 2yrs ago for any Selected Year.

We use the below DAX formula for this task - 

Sales 2 Years Ago = CALCULATE([Total Sales], DATEADD('Date Table'[Date], -2, YEAR))

We have the following output for the year 2016(meaning the year 2014) - 

Sales for the Year 2014

 

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

We use the following DAX formulas for this - 

Moving_Average_3_Months_Profit =
CALCULATE (
AVERAGEX ( Sales, Sales[Total Revenue] - (Sales[Total Unit Cost] * Sales[Order Quantity])),
DATESINPERIOD (
'Date Table'[Date],
LASTDATE ( 'Date Table'[Date]),
-3,
MONTH
)
)
Moving_Average_3_Months_Sales =
CALCULATE (
AVERAGEX ( Sales, Sales[Total Revenue] ),
DATESINPERIOD (
'Date Table'[Date],
LASTDATE ( 'Date Table'[Date]),
-3,
MONTH
)
)

I chose 3 months moving average as it depicts the trend accurately.

Let's see the output now - 

Moving 3 months average in terms of profit and sales

A date table will make the analysis stable.

We create a date table by the following MQuery - 

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
    AddFY
in
    fnDateTable

We get the output as follows - 

Date Table

The date table is very useful in terms of time intelligence functions.

The quarter-wise analysis is also important, maybe slicer.

We create a slicer for this as follows - 

Quarter Selection

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

We already have calculated all these values in previous tasks as new calculated measures. Let's use those values in the combo chart. We combine the line chart and card here by making the background of the line chart and card transparent. Let's see the output now - 

Total Sales, Profit and % Profit showed as a combo with card and line chart.

Here, the profit margin value is shown in a card while the other 2 values are shown in a line chart.

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

We use the below DAX formulas for this - 

Profit Cumulative TotalYTD = TOTALYTD(SUMX(Sales, (Sales[Total Revenue] - (Sales[Total Unit Cost] * Sales[Order Quantity]))), 'Date Table'[Date])
Profit Cumulative TotalYTD LY = TOTALYTD([Profit], SAMEPERIODLASTYEAR('Date Table'[Date]))

We get the following output if we select the year 2015 in Year Slicer - 

Cumulative Performance vs Cumulative Performance LY for the Year 2015

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

We show the Top 7 Products in terms of sales. We do that by using the following visual filter following - 

Top N Filter

 

We get the following output as a text table - 

Top 7 Products in terms of Total Revenue

In the same fashion, we can show the top 7 customers.

Performance in terms of region sales needs to workout and also needs to work on what will be the best way to visualize it except Bar Graph.

We show this by using Map visual. We get the output as follows - 

Sales in terms of regions

Before generating the above output, we first create a calculated column in the sales table as follows - 

Region City = RELATED(Regions[City])

For this formula to work, we need to have a relationship between tables as follows - 

Data Model

With this, we have completed our first project.

Student Survey Analysis

Let's discuss the dataset first. We have data related to the type of products students buy from various places and stores. A snapshot of the data is as follows - 

Student Survey Data

Now, let's discuss the requirements - 

The problem statement is as follow - 

There are many stores in which a survey was conducted based on students i.e. 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), the Store setting is the column that explains the type of location in which the store is present. By using the data set (Student Survey), try to extract meaningful Insights.

 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 do this as follows by going into formatting for a text table. We choose the field formatting option and do the following - 

Conditional Formatting in Advanced Control

We get the output as follows - 

TAP based on Store Location and Store Setting

I further changed the icons and fonts in formatting depending on the TAP values. You can explore them and change them accordingly.

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.

We do a similar thing from the above task by going into conditional formatting. We do something as follows - 

Conditional Formatting

We get the following out

Amount spent across different ages and store settings

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

For this, we choose the Funnel Chart. We do the formatting as follows - 

Data Colors Formatting

To show the values as percentage of first, we do the following - 

To show as a percentage of first

We get the final output as follows - 

Funnel Chart

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

We do this as follows - 

As we want the output for only the SubUrban Store setting, we use the visual level filter as follows - 

Visual Level Filtering

Then we get the final output for only the Suburban store setting as follows - 

Total Amount of Purchases by Store Location for Suburban Store Setting

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

b) Sand dance plot - Indoor sports and Video games spent across the different age groups.

The first requirement is simple, we just need to use a scatter plot with the respective values. We get the output as follows - 

Video games purchase and Outdoor sports spent across the different ages

The data labels depict the age average at that particular point. The colour formatting shows the divergence from the lowest age(red) to the highest age(blue). The size of the bubble also depict age.

Now, let's come to the sanddance plot. This is a custom visual which we get as follows - 

Then we search for Sandance plot. After that, we add it as follows by clicking on "Add"  - 

PowerBI Custom Visual

After adding the Sanddance plot to the report, we implement the following settings - 

We get the final output as follows - 

The sanddance plot is very customizable and it can be easily done by the end-user. Let's see the same visual in 2d - 

SandDance Plot in 2d

Restrict data access for the given users in the User mapping table.

We have the following requirements for this - 

User Mapping

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

We can test the roles by selecting the "View as" option from the Modelling section. Let's view the data as Ashok. 

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 six times every 4 hours for the Dashboard in a day.

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

We create the dashboard as follows - 

Dashboard

We implement the schedule refresh as follow - 

First, we install the Gateway connection. Then, we switch on the data gateway as below - 

We schedule the refresh as follows - 

Use Q&A feature of Power BI –

a) To show the average age of students

b) Donut chart for the total amount of purchases by ‘Store location’

We implement this in the Q&A feature of the dashboard. 

Let's see the final report and dashboard respectively.

Student Survey Report
Student Survey Analysis Dashboard

With this, we are done with our 2nd project.

Product Development

As usual, let's discuss the dataset first. We have vast information about different products with their categories, sub-categories, their resellers, sales details, employee details, customer details, region and product promotion information. Now, let's move on to the requirements - 

The problem statement is as follows - 

Essies Ltd is launching a new product and in the initial stage, they are targeting some segmented customers and regions. David 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 Subcategory table to be kept as a separate table.

We already have a product table. So, we create a date table with the following MQuery - 

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
    AddFY
in
    fnDateTable

We already have the product category and product sub-category as separate tables. Let's have a look at the data model. The relationships should be correct between different tables in order to achieve the next set of results. 

Now, let's move on to the next requirement - 

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

We add a calculated column in the RetailSales table with the following MQuery - 

if [SalesAmount] > 0 and [SalesAmount] <= 600 then "Low" 
else if [SalesAmount] > 600 and [SalesAmount] < 1300 then "Mid"
else "High"

We get the output as follows - 

David needs to have an Interactive slicer in terms of Country Flag in the report.

For this, we use a custom visual that is Enlighten World Flags. 

We get the output as follows by putting the country name from the Region table in the Country field.

Essies Dashboard needs to highlight the following:-

o Units Sold by Category

o Total Unit sold

o Retail Unit Sold

o Sales Unit Sold

First, let's create all the calculated measures as follows - 

Internet Qty Sold = SUM('Sales Details'[OrderQuantity])
Reseller Qty Sold = SUM('Retails Sales'[OrderQuantity])
Total Units Sold = [Internet Qty Sold] + [Reseller Qty Sold]

Now, let's visualize the output - 

• Among all the new Retailer top 10 should be highlighted in terms of unit sold

• Top performing products in terms of Unit sold

• Top performing in subcategories in terms of Unit sold

As we have discussed the Top N filter previously, we will not reiterate that point.

Let's see each of the output - 

As we have the data model relationships correct, the above charts can be made very easily.

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 of Components, Accessories, clothing, Bikes.

• Unit sold in terms of the retail counter.

Let's directly visualize the output as it can be very easily achieved by drag and drop. 

• Unit sold in terms of the retail counter -> 

This point was achieved in the previous task as retail counters and resellers are the same.

Let's see the final report for this project.

Final Report

With this, we have completed the 3rd project.

I thank Amit Bose and all the members of iNeuron.ai for their support. Please let me know if anyone requires the datasets used in these projects.

My LinkedIn ID is linkedin.com/in/urvindesai and my GitHub ID is github.com/urvindesai/

Please feel free to follow me for future updates.

Thank you!

Comments
Read next