Skip to content
/ Powerbi Public

This repository is to showcase my power BI portfolio

Notifications You must be signed in to change notification settings

art207/Powerbi

Repository files navigation

Power BI Reports

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

Emergeny Service Analysis Img1.png Emergeny Service Analysis Img2.png Emergeny Service Analysis Img3.png Emergeny Service Analysis Img4.png

Data Model

Emergency Service Analysis data model.png

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)] )

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

Hotel Revenue img1.pngHotel Revenue img2.pngHotel Revenue img3.pngHotel Revenue img4.png

Data Model

Hotel Revenue Datamodel.png

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] )

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

Executive Sales Report img1.pngExecutive Sales Report img2.pngExecutive Sales Report img3.png

Data Model

Executive Sales Report Data Model.png

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)

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

Consultancy Time and Earnings Analysis img1.pngConsultancy Time and Earnings Analysis img2.pngConsultancy Time and Earnings Analysis img3.png

Data Model

Consultancy Time and Earnings Analysis Data Model.png

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
Report Snapshots

Purchase Order Analysis img.png

About

This repository is to showcase my power BI portfolio

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published