Jump to content

Spot Rate Report

From PBI Reporting Wiki

Summary

This is a report that shows you a breakdown of the spot rates in the system

Filters

Filter Description
Database If you have access to multiple entity databases, you can filter and select specific ones.
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.Monthly Trend by Spot Rate Formula: 
Total Spot Rate Quotes =  

    CALCULATE( 

        COUNTROWS('Global quotation Spot Rate'),  

        FILTER( 

            'Global quotation Spot Rate', 

            'Global quotation Spot Rate'[spotrate] = 1 &&  

            NOT(ISBLANK('Global quotation Spot Rate'[quoted_at]))
This clustered column chart displays the monthly trends for Total Spot Rate Quotes, Total Spot Rate Bookings, and Total Spot Rate Confirmed.
2. Total Spot Rate Quotes. Formula: 
 Total Spot Rate Quotes =  

    CALCULATE( 

        COUNTROWS('Global quotation Spot Rate'),  

        FILTER( 

            'Global quotation Spot Rate', 

            'Global quotation Spot Rate'[spotrate] = 1 &&  

            NOT(ISBLANK('Global quotation Spot Rate'[quoted_at]))
  • Counts the number of spot rate quotes: It counts the rows in the 'Global quotation Spot Rate' table where the quote is classified as a spot rate (spotrate = 1).
  • Checks for a valid quote date: It ensures that only rows where the quoted_at column is not blank are included. This means it only considers quotes that have been officially recorded.The pre_quoted_at column is blank (no prequote was created).
  • Ensures the measure returns a numeric value: The + 0 ensures that the measure does not return a blank value in cases where no records match the criteria.


Purpose

This measure helps track the total number of valid spot rate quotes within a specific date range, providing insights into the volume of quotations generated.

3. Total Spot Rate Bookings. Formula: 
Total Spot Rate Bookings =  

    CALCULATE( 

        COUNTROWS('Global quotation Spot Rate'),  

        FILTER( 

            'Global quotation Spot Rate', 

            'Global quotation Spot Rate'[spotrate] = 1 &&  

            NOT(ISBLANK('Global quotation Spot Rate'[booked_at])) 

        ) 

    ) + 0
  • Counts the number of spot rate quotes: It counts the rows in the 'Global quotation Spot Rate' table where the quote is classified as a spot rate (spotrate = 1).
  • Checks for a valid booking date: It ensures that only rows where the booked_at column is not blank are included. This means it only considers quotes that have been successfully booked.
  • Ensures the measure returns a numeric value: The + 0 ensures that the measure does not return a blank value in cases where no records match the criteria.



Purpose

This measure helps track the total number of Total spot rate bookings within a specific date range,

4. Total Spot Rate Confirmed Formula: 
Total Spot Rate Confirmed =  

    CALCULATE( 

        COUNTROWS('Global quotation Spot Rate'),  

        FILTER( 

            'Global quotation Spot Rate', 

            'Global quotation Spot Rate'[spotrate] = 1 &&  

            NOT(ISBLANK('Global quotation Spot Rate'[confirmed_at])) 

        ) 

    ) + 0
  • Counts the number of spot rate quotes: It counts the rows in the 'Global quotation Spot Rate' table where the quote is classified as a spot rate (spotrate = 1).
  • Checks for a valid confirmation date: It ensures that only rows where the confirmed_at column is not blank are included. This means it only considers quotes that have been successfully confirmed.
  • Ensures the measure returns a numeric value: The + 0 ensures that the measure does not return a blank value in cases where no records match the criteria.
5.Table Breakdown By Total cost & sell 
SUM('Global quotation Spot Rate'[Total Cost])
 This table provides a breakdown of total cost and total sell for each Master Air Waybill (MAWB) by carrier. The Total Cost measure sums up all shipment costs using SUM('Global quotation Spot Rate'[Total Cost]), while the Total Sell measure calculates the total revenue using SUM('Global quotation Spot Rate'[Total Sell]). This helps track shipment expenses, monitor revenue, compare costs versus sell prices, and analyse carrier performance in terms of pricing.
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Spot_Rate_Report&oldid=105"