Jump to content

Quote & Bookings Summary Dashboard

From PBI Reporting Wiki

Summary

This page offers a comprehensive overview of all pre-quotes, quotes, and bookings made within the specified date range. The report focuses on weight breaks, providing a detailed analysis of the total Gross Weight and Chargeable Weight. It also includes a breakdown of customers linked to the bookings and the service types utilized. Additionally, the report highlights the reasons for lost bookings, offering valuable insights for further evaluation.

Filters

Filter Description
Database If you have access to multiple entity databases, you can filter and select specific ones.
Hide Lost / Deleted Hides lost or deleted jobs from the report.
Site Allows you to filter and focus on specific sites within your entity access.
Carrier Enables filtering by carriers associated with the sites you have access to.
Users Filters data by specific users. Users will be listed if they have access to the sites you can access.
Origin Filters jobs based on their origin location.
Destination Filters jobs based on their destination location.
Date Range Displays only jobs created within the specified date range.

Calculations

Reference Calculation Description
1 - Total Pre-Quotes Total Prequotes = CALCULATE(COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at]))) + 0 This measure counts the total number of prequotes created. It works by:
  • Checking if a date exists in the `pre_quoted_at` column (i.e., the quote was created).
  • Using the relationship between the `DimDate` table and the `pre_quoted_at` column to filter data for the selected time.
  • Counting the rows that meet these conditions.
  • Ensuring only valid prequotes with a date are included.
2 - Total Quotes Total Quotes = CALCULATE(COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[quoted_at]), NOT(ISBLANK(quotations[quoted_at]))) This measure counts the total number of finalized quotes created. Here's how it works:
  • Focuses on the quoted date: It checks the `quoted_at` column to find rows where a quote has a valid date (i.e., it was created).
  • Uses the date relationship: It temporarily activates the relationship between the `DimDate` table and the `quoted_at` column to filter data based on the selected date range.
  • Counts the valid quotes: It tallies up all rows where the `quoted_at` date is not blank, giving the total number of quotes.
  • Tracks issued quotes over time: This measure helps track how many official quotes were issued over a given period.
3 -Total Bookings

Total Bookings =  

CALCULATE ( 

COUNTROWS (quotations), 

NOT (ISBLANK (quotations [booked_at])),   

USERELATIONSHIP (quotations [booked_at], DimDate [Date]))

This measure counts the total number of bookings made. Here's how it works: 
  • Checks the booking date: It looks at the booked_at column in the quotations table to find rows where a booking date exists (i.e., the quote was successfully booked). 
  • Uses the date relationship: It temporarily activates the relationship between the DimDate table and the booked_at column to filter the bookings based on the selected time range in the report. 
  • Counts the rows: It counts all the rows where a valid booking date is present, giving the total number of bookings. 
4 - Confirmed Bookings Confirmed Bookings =  

CALCULATE ( 

    COUNTROWS (quotations), 

    NOT (ISBLANK (quotations [confirmed at])), 

    USERELATIONSHIP (DimDate [Date], quotations [confirmed at]))

This measure counts the total number of bookings that have been confirmed. Here's how it works: 
  • Checks for a confirmation date: It looks at the confirmed at column in the quotations table to find rows where a confirmation date exists (i.e., the booking was confirmed). 
  • Applies the date relationship: It temporarily uses the relationship between the DimDate table and the confirmed at column to filter data for the selected time range in the report. 
  • Counts the rows: It tallies up all rows where a valid confirmation date is present, resulting in the total number of confirmed bookings. 
  • This measure helps track the number of bookings that have been officially finalized and confirmed. 
5 - Converted Bookings Converted_Bookings =  

CALCULATE ( 

    COUNTROWS (quotations), 

    USERELATIONSHIP (DimDate [Date], quotations[booked_at]), // Temporarily activate the relationship with booked_at 

    (NOT (ISBLANK (quotations[quoted_at])) || NOT (ISBLANK (quotations[pre_quoted_at]))) &&  

    NOT (ISBLANK (quotations[booked_at])))  

The Converted Bookings formula calculates the total number of quotations that successfully converted into bookings:
  • The formula ensures that a booking is only counted if:
  • The quotation was either quoted (quoted_at) or pre-quoted (pre_quoted_at).
  • The quotation was successfully converted into a booking (booked_at).
6 - Direct Booking Direct_Booking =  

CALCULATE ( 

      COUNTROWS (quotations), 

     USERELATIONSHIP (DimDate [Date], quotations[booked_at]),  

    ISBLANK (quotations[quoted_at]),  

  ISBLANK (quotations[pre_quoted_at]), 

    NOT (ISBLANK (quotations[booked_at]))) 

This measure counts the total number of bookings that were made without going through a quote or prequote process. Here's how it works: 
  • Activates the booking date relationship: It temporarily uses the relationship between the DimDate table and the booked_at column to filter the data by the selected time range. 
  • Checks for no quotes or prequotes: It ensures that: 
  • The quoted_at column is blank (no quote was created). 
  • The pre_quoted_at column is blank (no prequote was created)
  • Confirms the booking: It verifies that the booked_at column is not blank, meaning a booking was finalized. 
  • Counts the rows: After applying these conditions, it counts the rows that meet the criteria. . 
7 - Conversion Rate Conversion Rate = DIVIDE([Converted_Bookings], [Total_Quotes],0)  This measure calculates the conversion rate, which shows the percentage of quotes that were successfully converted into bookings. Here's how it works: 
  • Numerator - Converted Bookings: It takes the total number of Converted Bookings, which are bookings that originated from quotes or prequotes and were successfully booked. 
  • Denominator - Total Quotes: It divides by the total number of Total Quotes, which represents all the quotes created. 
  • DIVIDE Function: The DIVIDE function performs the division and ensures that if the denominator (Total Quotes) is zero, it will return a result of 0 instead of an error. This handles cases where no quotes were created.

Purpose 

This measure helps track how effectively quotes are being turned into actual bookings. A higher conversion rate indicates a better success rate in converting quotes into bookings.  

Ref 3 50 ÷ 5 = 10 Example calculation for division
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Quote_%26_Bookings_Summary_Dashboard&oldid=20"