art207/powerbi

This repository is to showcase my power BI portfolio

Power BI Reports

Emergency Service Analysis

This is a completely random and anonymised data set

Analysis done through this dashboard

• No of calls

• Time between time of call and ambulance departure.

• Time taken for ambulance to arrive to patient.

• Time taken from patient house to hospital.

• No of jobs between specific period i.e over lunch times 12:00-14:00

• Does the despatch code affect the timings?

• Does the call handler have any impact?

• Do certain hospitals have issues in particular aspect

• What does the data say based upon the ambulance station?

• Does patient Sex affect handover times?

Report Snapshots

Data Model

Calculations

MEASURE_NAME	EXPRESSION
Hospital Capacity	SUM ( dimHospital[Capacity] )
No of Calls	COUNTROWS ( CallTimings )
Hospital Name	SELECTEDVALUE ( dimHospital[Hospital Name] )
Min Call Time	MIN (CallTimings[Length of Call (Mins)] )
Max Call Time	MAX( (CallTimings[Length of Call (Mins)] ))
Average Dispatch Time (ADT)	AVERAGE ( CallTimings[Average Dispatch Time] )
Min Date	FORMAT ( MIN ( DateTable[Date] ), "dd/mm/yyyy" )
Max Date	FORMAT ( MAX ( DateTable[Date] ), "dd/mm/yyyy" )
Female Calls	CALCULATE ( [No of Calls], dimPatient[Gender] = "Female" )
Male Calls	CALCULATE ( [No of Calls], dimPatient[Gender] = "Male" )
Average Ambulance Arrival Time (AAAT)	AVERAGE ( CallTimings[Average Ambulance Arrival Time] )
Average Hospital Arrival Time (AHAT)	AVERAGE ( CallTimings[Average Hospital Arrival Time] )
Patient Handover Time (PHT)	AVERAGE ( CallTimings[Average Hospital Handover Time] )
Average Handling Time (AHT)	Average ( CallTimings[Length of Call (Mins)] )

Hotel Revenue

This is a completely random and anonymised data set

Analysis done through this dashboard

• Time analysis exploration (Seasonality, Festive periods, weekday vs weekend etc)

• Agent’s performance, overview.

• Other insights providing further information on Customers type (family with children, single or couples visitors) etc.

Report Snapshots

Data Model

Calculations

Name	Expression
Record Count	COUNTROWS( 'fct_Hotel Revenue' )
Min Date	MIN( 'fct_Hotel Revenue'[Reservation Status Date] )
Max Date	MAX( 'fct_Hotel Revenue'[Reservation Status Date] )
Rev Rooms (Expected)	SUMX( 'fct_Hotel Revenue', 'fct_Hotel Revenue'[AVG Daily Rate] * 'fct_Hotel Revenue'[Nights (Tot)])
Rev Meals (Expected)	SUMX( 'fct_Hotel Revenue', 'fct_Hotel Revenue'[Meal Cost])
Total Revenue	[Rev Meals (Actual)] + [Rev Rooms (Actual)]
Total Nights Booked	sumx( 'fct_Hotel Revenue', 'fct_Hotel Revenue'[Nights (Tot)])
Total Nights Stayed	CALCULATE( [Total Nights Booked] , FILTER( 'dim_Reservation Status', 'dim_Reservation Status'[Reservation Satus] = "Check-Out"))
% Cancellations/No Shows	( [Total Nights Booked] - [Total Nights Stayed] ) / [Total Nights Booked]
Rev Rooms (Actual)	CALCULATE( [Rev Rooms (Expected)] , FILTER( 'fct_Hotel Revenue', OR( 'fct_Hotel Revenue'[Reservation Status Key] = 2 , 'fct_Hotel Revenue'[Deposit Type Key] = 2 )))
Rev Meals (Actual)	CALCULATE( [Rev Meals (Expected)] , FILTER( 'fct_Hotel Revenue', OR( 'fct_Hotel Revenue'[Reservation Status Key] = 2 , 'fct_Hotel Revenue'[Deposit Type Key] = 2 )))
Total Expected Revenue	[Rev Meals (Expected)] + [Rev Rooms (Expected)]
% Revenue Actual/Expected	[Total Revenue] / [Total Expected Revenue]
Revenue Wkly Moving Avg	VAR LastWeek = MAX( )
Check-Ins	CALCULATE( [Record Count] , FILTER( 'dim_Reservation Status', 'dim_Reservation Status'[Reservation Satus] = "Check-Out"))
Total Guests	CALCULATE( SUMX('fct_Hotel Revenue', 'fct_Hotel Revenue'[Adults] + 'fct_Hotel Revenue'[Babies] + 'fct_Hotel Revenue'[Children] ), FILTER( 'dim_Reservation Status', 'dim_Reservation Status'[Reservation Satus] = "Check-Out"))
Average Daily Rate	DIVIDE( SUMX( 'fct_Hotel Revenue', 'fct_Hotel Revenue'[AVG Daily Rate] * 'fct_Hotel Revenue'[Nights (Tot)] ) , sumx( 'fct_Hotel Revenue', 'fct_Hotel Revenue'[Nights (Tot)] ) )
% Rev from Meals	DIVIDE( [Rev Meals (Actual)] , [Total Revenue])
Revenue 1W Moving Avg	AVERAGEX( DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -7, DAY), [Total Revenue])
Cancellations 1W Moving Avg	averageX(DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -7, DAY), ( [Total Nights Booked] - [Total Nights Stayed] ) / [Total Nights Booked] )
Bookings 1Wk Moving Tot	SUMX( DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -7, DAY), [Total Nights Booked] )
Cancellations 1M Moving Avg	VAR tot = sumX(DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -1, MONTH), [Total Nights Booked] ) VAR stay = SUMX( DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -1, MONTH), [Total Nights Stayed] ) RETURN (tot-stay)/tot
Check-Ins 1W Moving Avg	AVERAGEX( DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -7, DAY), [Check-Ins])
Guests 1W Moving Avg	AVERAGEX( DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -7, DAY), [Total Guests])
Daily Rate 1W Moving Avg	averageX(DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -7, DAY), [Average Daily Rate] )

Executive Sales Report

This is a completely random and anonymised data set

Analysis done through this dashboard

• Sales trends
• Cumulative Sales
• 7 day Moving Average
• Sales by Channel
• Sales by Sales teams
• Sales by Region and State

Report Snapshots

Data Model

Calculations

MEASURE_NAME	EXPRESSION
Total Sales	SUMX( Sales , Sales[Unit Price] * Sales[Order Quantity] )
Total Quantity Sold	SUM( Sales[Order Quantity] )
Total Products Bought	DISTINCTCOUNT(Sales[Product Index])
Total Costs	SUMX( Sales , Sales[Unit Cost] * Sales[Order Quantity] )
Average Costs	AVERAGEX( Sales , Sales[Unit Cost] * Sales[Order Quantity] )
Average Sales	AVERAGEX( Sales , Sales[Unit Price] * Sales[Order Quantity] )
Total Profits	[Total Sales] - [Total Costs]
Profit Margin	DIVIDE( [Total Profits] , [Total Sales] , 0 )
Total Transactions	COUNTROWS( Sales )
Top 10 Cities by Profit	CALCULATE( [Total Profits] , FILTER( 'Store Locations' , 'Store Locations'[Top N Cities] = "Top 10" ) )
Prev. Month Sales	CALCULATE( [Total Sales] , DATEADD( Dates[Date] , -1 , MONTH ) )
Prev. Month Qty. Sold	CALCULATE( [Total Quantity Sold] , DATEADD( Dates[Date] , -1 , MONTH ) )
Sales Target	[Prev. Month Sales] * 1.1
Quantity Sold Target	[Prev. Month Qty. Sold] * 1.1
Avg. Retail Price	AVERAGE( Sales[Unit Price] )
Adjusted Retail Price	[Avg. Retail Price] * (1 + 'Price Adjustment (%)'[Price Adjustment (%) Value] )
Adjusted Sales	SUMX( Sales , [Adjusted Retail Price] * Sales[Order Quantity] )
Adjusted Profit	[Adjusted Sales] - [Total Costs]
% of Total Sales	DIVIDE( [Total Sales] , CALCULATE( [Total Sales] , ALL( Products[Product Name] ) ), 0 )
Price Adjustment (%) Value	SELECTEDVALUE('Price Adjustment (%)'[Price Adjustment (%)], 0)

Consultancy Time and Earnings Analysis

This is a completely random and anonymised data set

Analysis done through this dashboard

• How many productive hours doyou work in a week?

• How many should you be doing

• How many hours am I doing on a particular project

• How effective is the resource? How is it performing?

• Utilization

Report Snapshots

Data Model

Calculations

MEASURE_NAME	EXPRESSION
__Default measure	1
Duration Daily Average	AVERAGEX('Date', [Duration Sum])
Duration Hour Minute	VAR _Hour = HOUR( SELECTEDVALUE( atWorkData[Duration]) ) // Find minute as proportion of hour VAR _Minute = DIVIDE(MINUTE( SELECTEDVALUE( atWorkData[Duration] ) ), 60, 0 ) // Add together VAR _Result = _Hour + _Minute RETURN _Result
Duration Sum	SUM(atWorkData[Duration])
Total Business Hours	[Business Days] * 7.5
Total Earnings	SUM(atWorkData[Earnings, $])
Total Number of Fiscal Months	DISTINCTCOUNT( atWorkData[Year] ) * 12
Total Billed Hours	SUM ( atWorkData[Hours Billed] )
Business Days	CALCULATE( COUNT( 'Date'[Date] ) , 'Date'[IsBusinessDay] = TRUE() )
Working Days	DISTINCTCOUNT( atWorkData[Start] )
Number of Months Worked	DISTINCTCOUNT( atWorkData[YYYY-MM] )
Non Working Business Days	[Business Days] - [Working Days]
Total Non Working Fiscal Months	[Total Number of Fiscal Months] - [Number of Months Worked]
Non Working Business Hours	[Total Business Hours] - [Total Billed Hours]
Monthly Avg. Hours Billed	AVERAGEX ( VALUES ( 'Date'[Month & Year] ), [Total Billed Hours] )
Monthly Avg. Business Hours	AVERAGEX ( VALUES ( 'Date'[Month & Year] ), [Total Business Hours] )
Project Count	DISTINCTCOUNT( atWorkData[Project] )
Client Count	DISTINCTCOUNT( atWorkData[Client] )
Task Count	DISTINCTCOUNT( atWorkData[Task] )
Monthly Avg. Earnings	AVERAGEX ( VALUES( 'Date'[Month & Year] ), [Total Earnings] )
Hourly Billed Rate	DIVIDE( [Total Earnings] , [Total Billed Hours] )
Weekly Avg. Hours Billed	AVERAGEX( VALUES( 'Date'[Week & Year] ), [Total Billed Hours] )
Total Working Hours	[Working Days] * 7.5
Working Hours vs Billed Hours	[Total Billed Hours] - [Total Working Hours]
Weekly Avg. Working Hours	AVERAGEX( VALUES( 'Date'[Week & Year] ), [Total Working Hours] )
Working vs Business Hours	IF( [Total Working Hours] < [Total Business Hours], "Under Utilized Hours : " & [Total Business Hours] - [Total Working Hours], " Over Time Hours : " & [Total Working Hours] - [Total Business Hours] )
Weekly Avg. Earnings	AVERAGEX ( VALUES( 'Date'[Week & Year] ), [Total Earnings] )
Business Days without Work %	DIVIDE( [Non Working Business Days] , [Business Days] )
Months Without Work %	DIVIDE( [Total Non Working Fiscal Months] , [Total Number of Fiscal Months] )
Hours without Work %	DIVIDE( [Non Working Business Hours], [Total Business Hours] )
Utilization % | Business Hours	DIVIDE( [Total Billed Hours] , [Total Business Hours] )
Monthly Avg. Utilization %	DIVIDE ( [Monthly Avg. Hours Billed] , [Monthly Avg. Business Hours] )
Utilization % | Working Hours	DIVIDE( [Total Billed Hours] , [Total Working Hours] )
Weekly Avg. Utilization % (Working Days)	DIVIDE ( [Weekly Avg. Hours Billed] , [Weekly Avg. Working Hours] )
Working Hours vs Billed Hours %	[Working Hours vs Billed Hours] / [Total Working Hours]
Monthly Earning Tooltip Title	Var SelectedFY = Selectedvalue( 'Date'[Fiscal Year] ) Return " Monthly Earnings | For : " & SelectedFY
Month & Year	Var SelectedMonthNYear = Selectedvalue( 'Date'[Month & Year] ) Return SelectedMonthNYear
Tooltip Target	Var SelectedTarget = Selectedvalue( 'Utilization Target'[Target] ) Return SelectedTarget

Purchase Order Analysis

Report Snapshots

Sales Analysis

Call Center Analysis