Jump to content

Quotes Summary (W+M)

From PBI Reporting Wiki

Summary

This page provides a comprehensive historical overview of quotes (type = 8), excluding lost and deleted jobs, aggregated on a weekly and monthly basis. It highlights key business KPIs, including:

  • Total quotes
  • Number of customers
  • Percentage of won quotes
  • Quoted gross profit
  • Total quoted sell value (local currency)
  • Total quoted revenue (local currency)
  • Chargeable weight
  • Revenue
  • Gross profit
  • Routes
  • Margin

This report serves as a central reference for monitoring quotation performance and evaluating business trends over time.

Filters

Filter Description
Routes Filter the quotations by origin destination route pair
Customer Filter the quotations by customer name
Weekly/Monthly Button Enables users to switch between weekly and monthly

Data Transformation Applied

[To be added]

Query Calculations Description
Quote revenue from reporting database SELECT * FROM reporting_db_quote_revenue The query retrieves data from the quote revenue table in the reporting database.
Quotation table SELECT

   q.id,

   q.booked_at,

   q.confirmed_at,

   q.quoted_at,

   q.pre_quoted_at,

   q.created_at,

   q.user_id,

   q.booking_user_id,

   q.lost_reason,

   q.customer_id,

   q.customer_code,

   q.shipper_status,

   q.main_carrier_id,

   q.deleted_at,

   q.lost_at,

   q.delete_reasons,

   q.chargeable_weight,

   q.gross_weight,

   q.spotrate,

   q.type,

   q.schedule_auto_update,

   q.automation_created_count,

   q.schedule_auto_amount,

   q.status,

   q.booking_sync_state,

   q.cargo_type,

   q.house_airwaybill,

   q.product,

   q.origin,

   q.destination,

   q.master_airwaybill,

   q.hazardous_goods,

   q.commodity_description,

   q.weight_unit,

   q.cargo_only,

   CASE

       WHEN q.weight_unit = 'lb' THEN ROUND(q.chargeable_weight * 0.453592, 2)

       ELSE q.charge_weight

   END AS chargeable_weight_kg

FROM quotations AS q

WHERE q.created_at >= '2024-01-01'

  AND q.type = 8

  AND q.lost_at IS NULL

  AND q.deleted_at IS NULL;


This query retrieves columns from the quotation table and converts the chargeable weight from pounds to kilograms. It filters records where the creation date is on or after 2024 and the type equals 8, excluding lost and deleted entries.
Sites SELECT id, name FROM sites This selects site id and name from the site table.
Customer SELECT id, name FROM customers This query selects customer id and name from customer table
Users SELECT id, name FROM users This query selects User id and name from user table

Calculations

Reference Calculation Description
Quoted Customers 
Quoted Customers = 
COALESCE(
CALCULATE(
    DISTINCTCOUNT(Quotation[customer_id]),
    NOT ISBLANK(Quotation[quoted_at])&& 
    NOT ISBLANK(Quotation[id]),
    Quotation[type]=8),0)
 Calculates the number of unique customers who received at least one valid quote

The measure counts distinct Quotation[customer_id] values where all of the following conditions are met:

  • Quoted timestamp is not blank (quoted_at is not null)
  • Quotation ID is not blank (id is not null)
  • Quotation type equals 8

If no records match these conditions, the measure returns 0.

Won Quote % 
Won Quote24 % = 
COALESCE(
    VAR TotalQuotes =
        CALCULATE(
            DISTINCTCOUNT(Quotation[id]),
            NOT ISBLANK(Quotation[quoted_at]),
            Quotation[type] = 8,
            ISBLANK(Quotation[deleted_at]),
            ISBLANK(Quotation[lost_at]),
            Quotation[created_at] >= DATE(2024, 1, 1)
        )

    VAR ConvertedBookings =
        CALCULATE(
            DISTINCTCOUNT(Quotation[id]),
            NOT ISBLANK(Quotation[quoted_at]),
            NOT ISBLANK(Quotation[booked_at]),
            Quotation[type] = 8,
            ISBLANK(Quotation[deleted_at]),
            ISBLANK(Quotation[lost_at]),
            Quotation[created_at] >= DATE(2024, 1, 1)
        )

    RETURN DIVIDE(ConvertedBookings, TotalQuotes, 0)
,0)
 The formula calculates the conversion rates of quotes showing quotes that successfully resulted in booked or confirmed bookings.
Chargeable weight quoted in kg 
Chargeable_Weight quoted kg = 
COALESCE(
CALCULATE(
    SUM(Quotation[chargeable_weight_kg]),
    NOT ISBLANK(Quotation[quoted_at]),
    Quotation[type]=8),0)
 Chargeable_Weight Quoted (kg) calculates the total chargeable weight, in kilograms, associated with all valid quoted shipments.
Total Revenue quoted in euro 
Revenue Quoted EUR = 
COALESCE(
 CALCULATE(
    SUM('Quotation'[reporting_db quote_revenue.Quoted_customer_invoice_currency_sell_total_Euro]),
    NOT ISBLANK(Quotation[quoted_at])),0)
 Revenue Quoted (EUR) calculates the total quoted revenue in euros for all valid quotes.
Won Gross Profit euro 
Won Gross Profit (EUR) = 
COALESCE(
    SUMX(
        FILTER(
            'Dimreporting_db quotation_totals',
            'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
            && NOT ISBLANK('Dimreporting_db quotation_totals'[quoted_at])
            && NOT ISBLANK('Dimreporting_db quotation_totals'[booked_at])
        ),
        ('Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
         - 'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total])
        / 'Dimreporting_db quotation_totals'[booked_customer_invoice_currency_to_euro_exchange_rate]
    ),
0)
 This measure calculates the total gross profit, in euros, for all quotations that have progressed to a won/booked status. It includes only quotations created from 1 January 2024 onward and ensures that both quotation and booking dates are present. Gross profit is converted to EUR using each transaction’s booking exchange rate.
Quoted Gross profit euro 
Quoted Gross Profit (EUR) = 
COALESCE(
    SUMX(
        FILTER(
            'Dimreporting_db quotation_totals',
            'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
            && NOT ISBLANK('Dimreporting_db quotation_totals'[quoted_at])
        ),
        ('Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
         - 'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total])
        / 'Dimreporting_db quotation_totals'[quoted_customer_invoice_currency_to_euro_exchange_rate]
    ),
0)
 This measure calculates the total gross profit, expressed in euros, for all quotations issued from 1 January 2024 onward. Only quotations with a valid quotation date are included. It standardizes gross profit values by converting them from the customer’s invoice currency into EUR using each quotation’s specific exchange rate.
Total Sell LCY(quoted) 
Direct connection to the reporting database for the quote field: site_currency_sell_total
 site_currency_sell_total represents the total selling amount of a quotation in the original site (local) currency. This value is pulled directly from the reporting database without transformation, ensuring consistency with the source quoting system.
Total Sell Rate LCY(quoted) 
Direct connection to the reporting database for the quote field: site_sell rate
 site_sell_rate represents the selling rate applied to the quotation in the original site (local) currency. The field is sourced directly from the reporting database without modification, ensuring alignment with the values entered in the quoting system
Routes (o +D) 
Origin_dest_Coount quoted = 
COALESCE(
 CALCULATE(
    DISTINCTCOUNT(Quotation[Origin _ Destination]),
    NOT ISBLANK(Quotation[quoted_at]),
    Quotation[type] =8),0)
 This measure calculates the number of unique Origin _ Destination entries from the Quotation table where a quotation has been provided (quoted_at is not blank) and the quotation type equals 8. If no such entries exist, the measure returns 0.
Origin destination

pair 

 each [origin] & " - " & [destination]
 The formula concatenate origin column and destination column
Origin destination

pair 

if [product] = 0 then "AA" 
else if [product] = 1 then "AD" 
else if [product] = 2 then "DA" 
else if [product] = 3 then "DD" 
else "Unknown")
 This rule assigns a product category code based on the numeric value stored in the product field. It standardizes product identifiers into readable, business-friendly labels.
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Quotes_Summary_(W%2BM)&oldid=404"