Detail (W+M)
Summary
This page presents a comprehensive historical analysis of Booked and Confirmed bookings (type = 8), excluding lost and deleted jobs, on a weekly and monthly basis. It highlights key business KPIs, including jobs, customers, chargeable weight, revenue, gross profit, and margin.
Filters
| Filter | Description |
|---|---|
| Sites | filter by site name |
| Weekly/Monthly Button | Enables switching between weekly and monthly values. |
Data Transformation Applied
[To be added]
| Query | Calculations | Description |
|---|---|---|
| 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. |
| reporting db quotation totals | SELECT * FROM reporting_db_quotation_totals | This selects data from reporting_db_quotation_totals |
| 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 |
|---|---|---|
| Total Revenue in euros booked or confirmed | Total Revenue (EUR Booked or Confirmed) =
COALESCE(
SUMX(
FILTER(
'Dimreporting_db quotation_totals',
'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
),
SWITCH(
TRUE(),
NOT ISBLANK('Dimreporting_db quotation_totals'[booked_at]),
'Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
/ 'Dimreporting_db quotation_totals'[booked_customer_invoice_currency_to_euro_exchange_rate],
ISBLANK('Dimreporting_db quotation_totals'[booked_at])
&& NOT ISBLANK('Dimreporting_db quotation_totals'[confirmed_at]),
'Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
/ 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate],
0
)
),
0) |
The formula calculates the total revenue in euros where either the booked timestamp or the confirmed timestamp is not null. |
| Gross Profit in euros booked or confirmed | Gross Profit (EUR booked or Confirmed)2 =
COALESCE(
SUMX(
FILTER(
'Dimreporting_db quotation_totals',
'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
),
SWITCH(
TRUE(),
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],
ISBLANK('Dimreporting_db quotation_totals'[booked_at])
&& NOT ISBLANK('Dimreporting_db quotation_totals'[confirmed_at]),
('Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
- 'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total])
/ 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate],
0
)
),
0) |
The formula calculates the gross profit in euros where either the booked timestamp or the confirmed timestamp is not null. |
| Total Chargeable Weights in kg | Chargeable_Weight quoted kg =
COALESCE(
CALCULATE(
SUM(Quotation[chargeable_weight_kg]),
NOT ISBLANK(Quotation[quoted_at]),
Quotation[type]=8),0) |
The formula calculates the total chargeable weight in kilograms |
| Total Margin in euros % | Margin (%) =
DIVIDE(
[Total Revenue (EUR Booked or Confirmed)] - [Total Cost (EUR booked or confirmed)],
[Total Revenue (EUR Booked or Confirmed)],
0
) |
The formula calculates the total margin in euros where either the booked timestamp or the confirmed timestamp is not null.
|