Power BI Projects - Solutions to Business Problems

Anjana A Dec 29 2020 · 12 min read
Share this

                 

Microsoft Power BI is a cloud-based business analytics service that gives us a single view of most critical business data. This blog deals with a collection of 3 different projects covering three different scenarios and their way of approach, how to analyze and finally visualize them with the help of available features in Power BI.

                                                Project 1 – Acquisition

Problem statement

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 the Total Sales?

• What about the Total Quantity.

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

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

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

• A date table will make the analysis stable.

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

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

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

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

• Performance in terms of region sales needs to work out and also needs to work on as what will be the best way to visualise it except Bar Graph.

Source data inferences

Given "Optiveriance" Excel Data consisting of following tables - Customer Data table, Metric Selection table, Product Data table, Regions table, Sales table. The tables do not require further transformation.

As a best practise, we can keep measures under a single measure table, ‘Imp. Measures’ -- > Under Home section, click on Enter data and name the table – Imp Measures.

Solution

Total Sales

We have the revenue column readily available in the dataset. Hence, we can calculate the total sales using the formula.

Total Sales = SUM(Sales[Total Revenue])

Total Quantity

Similarly for quantity, we can sum up the quantity column to get the output.

Total Quantity = SUM(Sales[Order Quantity])

Profit for the said period and the margin on the same

Using the below expressions, we can calculate profit and profit margin.

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

Profit Margin = DIVIDE([Profit],[Total Sales],0)

Convert profit margin into % using the option Measure tools on the top toolbar. Under Measure tools section, click on % icon.

Figure 1 - Visualizing Total Sales, Total quantity, Profit and Profit Margin using card

Performance LY for any selected Year

Inorder to proceed with the requirement, we shall first work on creating a date table to create reference.

Before creating Date table, we need to know the range of dates, ie, "Start Date" and "End Date". Using OrderDate column, we need to decide the date range.

Create a date column ranging from 01-01-2014 to 31-12-2016 using the M Query in Power Query Editor > Blank Query > Advanced Editor.



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

Mark the table as date table in data view.

Need to have a details Performance LY for any selected Year

As a best practise, we can create a time intelligence measures table inorder to keep the measures related to sales in different years.

We need to establish connection between tables before proceeding.

Figure 2 - Connection between tables

Sales last year is calculated using the DAX formula

Sales Last Year = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(DimDate[Date]))

Profit last year is calculated using the DAX formula

Profit Last Year = CALCULATE([Profit],SAMEPERIODLASTYEAR(DimDate[Date]))

Visualize the profit and sales using a table and a slicer to choose year.

Figure 3 - Profit and sales of last year

Comparing the profit and the performance LY for a sample year, 2016.

Figure 4 - Profit and performance last year

Cumulative performance VS Cumulative performance LY

Comparing sales and profit last year to Total sales to see the cumulative performance.

Figure 5 - Cumulative performance

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

This is created using custom visual - Card with States by OKVIZ.   It displays monthly wise profit margin for sales and profit as tooltip.

Figure 6 - Monthly wise profit margin for sales as tooltip
Figure 7 - Monthly wise profit margin for profit as tooltip

Sales 2yrs ago for any Selected Year

The following DAX formula is used to calculate the sales 2 years ago for any selected year.

Sales 2yrs Ago = CALCULATE([Total Sales],DATEADD(DimDate[Date],-2,YEAR))

Visualize the sales using a table and the slicer already present in the report.

Figure 8 - Sales 2 years ago

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

In terms of sales, moving average will be

Sales Moving Average =

AVERAGEX(

    FILTER(ALLSELECTED(DimDate),

    DimDate[Date]<=MAX(DimDate[Date])),

    [Total Sales])

In terms of profit, moving average will be

Profit Moving Average =

AVERAGEX(

    FILTER(ALLSELECTED(DimDate),

    DimDate[Date]<=MAX(DimDate[Date])),

    [Profit])

Figure 9 - Moving average

Quarter wise analysis is also important, may be slicer.

Selecting any quarter from the slicer displays the quarter sales for all years.

Figure 10 - Quarter wise sales

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

As a best practise, we should keep the measures used under a single table. Name it Specific scenario

Calculate top 7 customers using below DAX formula:

Top 7 Customers = CALCULATE([Total Sales],FILTER(VALUES('Customer Data'[Customer Names]),

IF(RANKX(ALL('Customer Data'[Customer Names]),[Total Sales],,DESC)<=7,[Total Sales],BLANK())))

Calculate top 7 products using below DAX formula:

Top 7 Products = CALCULATE([Total Sales],FILTER(VALUES('Product Data'[Product Name]),

IF(RANKX(ALL('Product Data'[Product Name]),[Total Sales],,DESC)<=7,[Total Sales],BLANK())))

Figure 11 - Top 7 customers and products

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

We can better visualize the above requirement using a map visual.

Territory column - Location field

Total Sales measure - Size field

Total Profit and Profit Margin measures - Tooltips field

Figure 12 - Total sales, profit and profit margin by territory

Dashboard

Figure 13 - Dashboard - Acquisition

Insights

·         Products such as Brimmer, Nicel, Dimonies, Werrier, Linon, Qusec and loader are more sold compared to last year sales and for products  such as Assitus, Denycons, Penter, Unicoes, Ferrous, Channels and Carvetures shows more sales than current year.

·         Brimmer is in high demand and Nicel has contributed to more profit in terms of current and previous year sales.

·         Carvetures is in low demand and Channels have contributed to more profit in terms of current and previous year sales.

                                                              Project 2 – Spend

Requirements

Problem Statement- 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. Then create a schedule refresh for six times in every 4 hours for the Dashboard in a day.

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’

Source data inferences

A survey result conducted based on students through stores to get the details about 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), we need to extract the meaningful Insights.

The dataset contains 2 tables - Student Survey, User Mappings

Tabular visualization

We can represent this using ‘Table’ visual under Visualizations pane.

Choose Store Location, Store Setting and Total Amount of Purchases columns to values field.

To perform conditional formatting as per the requirement, go to Format section of Visualizations pane and click on Conditional Formatting option, select ‘Total Amount of Purchases’ from the dropdown list.

Turn background colour slider option to ON. Click on Advanced controls option

A window pops up which allows us to format background colour of ‘Total Amount of Purchases’ by Rules. Select Format by type as Rules and gives the conditions as per the requirement and click on OK.

Figure 14 - Conditional formatting  -  TAP
Figure 15 - TAP by store details

Matrix visualization

Choose matrix visualization to meet the requirement. Choose Age in row fields, Store settings in column field and choose OutDoor SportKits in values field.

In format section of visualizations pane, click on Conditional Formatting option and select ‘OutDoor SportKits’ from the dropdown list.

Turn background colour slider option to ON. Click on Advanced controls option

A window pops up which allows us to format background color of ‘Total Amount of Purchases’ by Rules. Select Format by type as Rules and gives the conditions as per the requirement and click on OK.

Figure 16 - Conditional formatting based on OutDoor SportKits
Figure 17 - Amount spent - Outdoor sports

Funnel chart

Select funnel chart to visualize the requirement. Total amount of purchase by ‘Store setting’

Choose "Store Setting" to Group field and "Total Amount of Purchases" to Values field. Turn the data labels to ON, select Label style as Percent of first from the dropdown list.

Figure 18 - TAP by store setting

Pie chart

Select Pie chart to visualize ‘Store location’ for Suburban ‘Store setting’.

Choose store location for legend and Total amount of purchase for values. In filters section, choose store setting and tick suburb.

Figure 19 - Filter based on store setting
Figure 20 - TAP by store location

Scatter plot

Choose scatter plot for representing the requirement. Choose Age for details, X- Axis as OutDoor SportKits and Y- Axis as Video games.

Figure 21 - Outdoor and Indoor Games by Age

Sand dance plot

Import the custom visual – Sand dance plot 2019. Select the visual and fill the x-axis, y-axis, color by and sort by fields.

Figure 22 - Filter for sand dance plot
Figure 23 - Sand dance plot

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

Requirement is –

·         Mani deals with Rural only.

·         Nani deals with Urban only.

·         Nitin deals with Suburb only.

·         Ashok deals with Rural only.

Create a table with store location, store setting and TAP column. Create roles as per the above requirement based on filters applied to ‘Store setting’ column. View the store setting through the eyes of different roles.

Figure 24 - Different roles assigned
Figure 25 - Viewing using roles

Dashboard

Figure 26 - Dashboard - Spend Analysis

Insights

·         Average age of students are around 14 years.

·         New York store location generates high purchase (26.11% ). While Boston store location generates low purchase (22.81%)

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.

Publish the report to the workspace – ‘Spend Analysis’ created earlier in Power BI Service.

Figure 27 - Published the dashboard to workspace - Spend Analysis

To schedule refresh in power bi service

Under Workspaces, go to Spend Analysis workspace. Under Datasets click on the 3 dots next to dataset name and select Settings option.

It navigates to Gateway connection where if we don't have On-premises data gateway application installed, it prompts to install now. Once it is installed sign in with Power BI credentials and navigate to the same screen for further actions.

Edit Data Source Credentials and choose privacy level setting for this data source as private. Now, go to the option Schedule Refresh and give the schedule as per requirement, i.e , every 4 hours. We can set failure notification email to alert us in case if the schedule fails.

Figure 28 - Scheduling refresh in Power BI service

Use Q&A Feature:

a)      To show average age of students

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

We can use the Q&A option available in Edit section to use avail the option.

Figure 29 - Q & A section 

                                                Project 3 – Essies ltd

Requirements

Essies Ltd is launching a new product and in the initial stage they are targeting some segmented customer and region. 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 Sub category table to be kept as separate table.

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

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

• Among all the new Retailer top 10 should be highlighted

• Top performing products in sales

• Top performing in subcategories.

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

• Unit sold in terms of retail counter.

Source data inferences

The source data ’EssiesLtd’ Excel Data consists of following tables - Currency, Cust, Emp_Details, Product Promotion, Region, Reseller, RetailSales, RetailsCounters, Sales Details, Sales Territories.

Transformations required

It is important to transform data before loading them. Following are the transformations required.

·         Removed null columns ‘Address Line2’ and ‘Title’ from table ‘Cust’. Replaced Title with a custom column using the formula -

if [Gender]="F" and [MaritalStatus]="S" then "Ms."else if [Gender]="F" and [MaritalStatus]="M" then "Mrs."else if [Gender]="M" then "Mr." else "Other"

·         Created a column with customer name by merging Title, First Name and Last Name.

·         Change data types into relevant forms for columns accordingly.

·         Removed ParentEmployeeNationalIDAlternateKey since the column is a complete blank column.

·         Merged First Name, Middle Name and Last Name columns to get employee full name.

·         Merged address line 1 &2 in Reseller table to get the address as a single column.

·         Removed CarrierTrackingNumber and CustomerPONumber columns as they are holding null values.

·         Created a Date Table in order to work on requirements related to dates.

Total units sold, Retail units sold, Sales units sold and units sold by Category

As part of best practise, created a measure table to keep all important measures used together.

After transformation of data, we need to ensure connectivity between tables.

Figure 30 - Data connectivity between tables

The requirements can be calculated using the below DAX formulae:

Retail Units Sold = SUMX(RetailSales,RetailSales[OrderQuantity])

Sales Units Sold = SUMX('Sales Details','Sales Details'[OrderQuantity])

Total Units Sold = [Retail Units Sold]+[Sales Units Sold]

The above requirements can be visualized separately using cards.

Figure 31 - Visualizing the units sold using card

Unit sold by category can be better visualized using a donut or a pie chart.

Figure 32 - Sales and Retail Units by Product Category

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

In retail sales table, create a new custom column in Power Query Editor with name ‘Sales Priority’ using the below formula:

if [SalesAmount]<500 then "Low Sales"

else if [SalesAmount]>500 and [SalesAmount]<1000

then "Mid Sales"

else if [SalesAmount]>1000 then "High Sales"

else "Others"

Resultant table –

Figure 33 - Table - Sorting by sales priority

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

Choose the columns English Promotion Category', 'Retail Units Sold', 'Sales Units Sold', 'Total Units Sold', 'Customer key' to visualize the requirements in a table.

Figure 34 - Unit sold in Promotion events in terms of reseller, no discount and Customers

Top performers in Product Category

As a best practise, keeping the requirement in a special measure table – Specific scenario

Top performers can be calculated using the DAX formula -

ProductCategory TopPerformers = CALCULATE([Total Units Sold],FILTER(VALUES('Product'[EnglishProductName]),

IF(RANKX(ALL('Product'[EnglishProductName]),[Total Units Sold],,DESC)<=10,[Total Units Sold],BLANK())))

Figure 35 - Top 10 performers in units sold

Top performers in Product Sub Category

SubCategory TopPerformers = CALCULATE([Total Units Sold],FILTER(VALUES('Product Sub-Category'[Subcategory]),

IF(RANKX(ALL('Product Sub-Category'[Subcategory]),[Total Units Sold],,DESC)<=10,[Total units Sold],BLANK())))

Figure 36 - Top 10 performers in subcategory

Interactive slicer in terms of Country Flag in the report

Invoke the custom visual ‘Enlighten World Flags slicer’ and choose English Country Region Name in Country field.

Figure 37 - Country wise slicer

Dashboard

Figure 38 - Dashboard - Essies LTD Analysis

Insights

·         Retail units are more sold compared to sales units.

·         Road bikes bags the top sales among subcategory and AWC logo cap is the best seller in product category.

There are multiple approaches to solve each business case. This is just an approach of analyzing and visually representing the solutions to these business problems.

I have utilized available features like custom visuals, DAX expressions, M Query, Power query editor, calculated measures, custom columns, conditional formatting etc. in Power BI desktop to create interactive dashboards and publish them to Power BI Service in order to derive possible insights. 

Comments
Read next