Jump to content

Pre-quote Report: Difference between revisions

From PBI Reporting Wiki
VisualWikitext
Created page with "== Summary == TBC == Filters == {| class="wikitable" |+ ! style="width: 25%;" |Filter ! style="width: 75%;" |Description |- |TBC |TBC |} == Calculations == none|thumb|800x800px {| class="wikitable" |+ !Reference !Calculation !Description |- | | | |}"
 
 
(7 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Summary ==
== Summary ==
TBC
This is a report that shows you a breakdown of the pre quoted, which were lost and which were turned into quotes or bookings.


== Filters ==
== Filters ==
Line 8: Line 8:
! style="width: 75%;" |Description
! style="width: 75%;" |Description
|-
|-
|TBC
|Database||If you have access to multiple entity databases, you can filter and select specific ones.
|TBC
|-
|Year||Allow you to select the year
|-
|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 quotes/bookings by specific users. Users will be listed if they have access to the sites you can access.
|-
|Date Filter||Displays only jobs created within the specified date range
|}
|}


Line 20: Line 29:
!Description
!Description
|-
|-
|
| 1.Total Prequotes  By  Month  ||Formula:
|
<pre>Total Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])) ) + 0 </pre>
|
||The Clustered bar chart breaks down the prequote by Month.
|-
| 2.Table breakdown By Total Pre quotes||Formula:
<pre>Total Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])) ) + 0 </pre>
||The table breaks down Total pre quotes, Deleted  Incorrect Pre quotes and Accuracy.
|-
| 3.Total Pre quotes By time|| Formula:
<pre>Total Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])) ) + 0 </pre>
||The table breaks down pre quotes created over Time by Mailbox
|-
| 4.Total Deleted Prequotes||
Formula:
<pre>Total Deleted Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])), NOT(ISBLANK(quotations[deleted_at])) ) + 0 </pre>
|| The table breaks down Delete Reasons by total deleted pre quotes
|-
| 5. 
Total Pre quotes
||Formula:
<pre>Total Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])) ) + 0</pre>
||
*Counts the number of prequotes: It counts the rows in the quotations table where the pre_quoted_at column is not blank, indicating a valid prequote.
 
*Uses the date relationship: It activates the relationship between the DimDate[Date] column and the quotations[pre_quoted_at] column, ensuring that the data is filtered by the selected time range.
 
*Ensures the measure returns a numeric value: The + 0 ensures that the measure doesn't return a blank value when there are no matching records.
 
Purpose
 
This measure helps track the total number of prequotes within a specific date range, providing insights into the total number of prequotes made. 
|-
| 6. Lost Pre quotes||Formula:
<pre>Total Lost Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])), NOT(ISBLANK(quotations[lost_at])) ) + 0 </pre>
||
*Counts the number of lost prequotes: It counts the rows in the quotations table where the pre_quoted_at column is not blank (indicating a valid prequote), and the lost_at column is not blank, indicating that the prequote has been lost.
 
*Uses the date relationship: It activates the relationship between the DimDate[Date] column and the quotations[pre_quoted_at] column, ensuring that the data is filtered by the selected time range.
 
*Ensures the measure returns a numeric value: The + 0 ensures that the measure doesn't return a blank value when there are no matching records.
 
Purpose
 
This measure helps track the total number of lost prequotes within a specific date range. 
|-
| 7.Deleted pre quotes||Formula:
<pre>Total Deleted Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])), NOT(ISBLANK(quotations[deleted_at])) ) + 0 </pre>
||
*Counts the number of deleted prequotes: It counts the rows in the quotations table where the pre_quoted_at column is not blank (indicating a valid prequote), and the deleted_at column is not blank, indicating that the prequote has been deleted.
 
*Uses the date relationship: It activates the relationship between the DimDate[Date] column and the quotations[pre_quoted_at] column, ensuring that the data is filtered by the selected time range.
 
*Ensures the measure returns a numeric value: The + 0 ensures that the measure doesn't return a blank value when there are no matching records.
 
Purpose
 
This measure helps track the total number of deleted prequotes within a specific date range, providing insights into the volume of prequotes that were removed 
|-
| 8.Pre quote to bookings|| <pre>Total Pre-quote to Converted = 
 
    CALCULATE(
 
        COUNTROWS(quotations),
 
      quotations[Booking_categories] = "Pre-quote to Booking"
 
    ) + 0 </pre>
||
*Counts the number of prequotes converted to bookings: It counts the rows in the quotations table where the Booking_categories column equals "Pre-quote to Booking," indicating that the prequote has been successfully converted into a booking.
*Ensures the measure returns a numeric value: The + 0 ensures that the measure doesn't return a blank value when there are no matching records.
Purpose
 
This measure helps track the total number of prequotes that have successfully been converted into bookings, providing insights into the conversion rate of prequotes to actual bookings. 
|-
| 9. Incorrect prequote||Formula:
<pre>Total Prequotes with IQ = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])), quotations[delete_reasons] = "IQ" ) </pre>
||
*counts prequotes with "IQ" delete reason: It counts the rows in the quotations table where the pre_quoted_at column is not blank (indicating a valid prequote), and the delete_reasons column equals "IQ," indicating that the prequote was deleted due to an incorrect quotation.
 
*Uses the date relationship: It activates the relationship between the DimDate[Date] column and the quotations[pre_quoted_at] column, ensuring that the data is filtered by the selected time range.
Purpose
 
This measure helps track the total number of prequotes with the "IQ" delete reason, providing insights into the volume of prequotes that were deleted due to IQ-related issues. 
|-
| 10.Prequote conversion
|| Formula:
<pre>Prequote Conversion = 
 
  DIVIDE([Total Pre-quote to Converted], [Total Prequotes]) </pre>
||
*Total Pre-quote to Converted: This represents the number of prequotes that were converted to bookings.
 
*Total Prequotes: This is the total number of prequotes made.
 
*DIVIDE function: It divides the number of converted prequotes by the total prequotes to calculate the conversion rate. The DIVIDE function ensures that any division by zero returns a blank result instead of an error.
 
Purpose
 
This measure helps track the conversion rate of prequotes to bookings, providing insights into how many prequotes are successfully turned into actual bookings.
 
 
|}
|}

Latest revision as of 14:07, 17 June 2025

Summary

This is a report that shows you a breakdown of the pre quoted, which were lost and which were turned into quotes or bookings.

Filters

Filter Description
Database If you have access to multiple entity databases, you can filter and select specific ones.
Year Allow you to select the year
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 quotes/bookings by specific users. Users will be listed if they have access to the sites you can access.
Date Filter Displays only jobs created within the specified date range

Calculations

Reference Calculation Description
1.Total Prequotes By Month Formula: 
Total Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])) ) + 0
The Clustered bar chart breaks down the prequote by Month.
2.Table breakdown By Total Pre quotes Formula: 
Total Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])) ) + 0
The table breaks down Total pre quotes, Deleted Incorrect Pre quotes and Accuracy.
3.Total Pre quotes By time Formula: 
Total Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])) ) + 0
The table breaks down pre quotes created over Time by Mailbox
4.Total Deleted Prequotes

Formula: 

Total Deleted Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])), NOT(ISBLANK(quotations[deleted_at])) ) + 0
The table breaks down Delete Reasons by total deleted pre quotes
5.

Total Pre quotes

Formula: 
Total Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])) ) + 0
  • Counts the number of prequotes: It counts the rows in the quotations table where the pre_quoted_at column is not blank, indicating a valid prequote.


  • Uses the date relationship: It activates the relationship between the DimDate[Date] column and the quotations[pre_quoted_at] column, ensuring that the data is filtered by the selected time range.
  • Ensures the measure returns a numeric value: The + 0 ensures that the measure doesn't return a blank value when there are no matching records.

Purpose

This measure helps track the total number of prequotes within a specific date range, providing insights into the total number of prequotes made.

6. Lost Pre quotes Formula: 
Total Lost Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])), NOT(ISBLANK(quotations[lost_at])) ) + 0
  • Counts the number of lost prequotes: It counts the rows in the quotations table where the pre_quoted_at column is not blank (indicating a valid prequote), and the lost_at column is not blank, indicating that the prequote has been lost.


  • Uses the date relationship: It activates the relationship between the DimDate[Date] column and the quotations[pre_quoted_at] column, ensuring that the data is filtered by the selected time range.


  • Ensures the measure returns a numeric value: The + 0 ensures that the measure doesn't return a blank value when there are no matching records.

Purpose

This measure helps track the total number of lost prequotes within a specific date range.

7.Deleted pre quotes Formula: 
Total Deleted Prequotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])), NOT(ISBLANK(quotations[deleted_at])) ) + 0
  • Counts the number of deleted prequotes: It counts the rows in the quotations table where the pre_quoted_at column is not blank (indicating a valid prequote), and the deleted_at column is not blank, indicating that the prequote has been deleted.


  • Uses the date relationship: It activates the relationship between the DimDate[Date] column and the quotations[pre_quoted_at] column, ensuring that the data is filtered by the selected time range.


  • Ensures the measure returns a numeric value: The + 0 ensures that the measure doesn't return a blank value when there are no matching records.

Purpose

This measure helps track the total number of deleted prequotes within a specific date range, providing insights into the volume of prequotes that were removed

8.Pre quote to bookings 
Total Pre-quote to Converted =  

    CALCULATE( 

        COUNTROWS(quotations), 

      quotations[Booking_categories] = "Pre-quote to Booking" 

    ) + 0
  • Counts the number of prequotes converted to bookings: It counts the rows in the quotations table where the Booking_categories column equals "Pre-quote to Booking," indicating that the prequote has been successfully converted into a booking.
  • Ensures the measure returns a numeric value: The + 0 ensures that the measure doesn't return a blank value when there are no matching records.


Purpose

This measure helps track the total number of prequotes that have successfully been converted into bookings, providing insights into the conversion rate of prequotes to actual bookings.

9. Incorrect prequote Formula: 
Total Prequotes with IQ = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[pre_quoted_at]), NOT(ISBLANK(quotations[pre_quoted_at])), quotations[delete_reasons] = "IQ" )
  • counts prequotes with "IQ" delete reason: It counts the rows in the quotations table where the pre_quoted_at column is not blank (indicating a valid prequote), and the delete_reasons column equals "IQ," indicating that the prequote was deleted due to an incorrect quotation.


  • Uses the date relationship: It activates the relationship between the DimDate[Date] column and the quotations[pre_quoted_at] column, ensuring that the data is filtered by the selected time range.

Purpose


This measure helps track the total number of prequotes with the "IQ" delete reason, providing insights into the volume of prequotes that were deleted due to IQ-related issues.

10.Prequote conversion Formula: 
Prequote Conversion =  

   DIVIDE([Total Pre-quote to Converted], [Total Prequotes])
  • Total Pre-quote to Converted: This represents the number of prequotes that were converted to bookings.


  • Total Prequotes: This is the total number of prequotes made.


  • DIVIDE function: It divides the number of converted prequotes by the total prequotes to calculate the conversion rate. The DIVIDE function ensures that any division by zero returns a blank result instead of an error.

Purpose

This measure helps track the conversion rate of prequotes to bookings, providing insights into how many prequotes are successfully turned into actual bookings.


Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Pre-quote_Report&oldid=102"