Jump to content

Detail (W+M): Difference between revisions

From PBI Reporting Wiki
← Older editNewer edit →
VisualWikitext
Line 134: Line 134:
! style="width: 20%;" | Reference  !! style="width: 40%;" | Calculation  !! style="width: 40%;" | Description
! style="width: 20%;" | Reference  !! style="width: 40%;" | Calculation  !! style="width: 40%;" | Description
|-
|-
| TBC ||<pre>TBC</pre>|| TBC
| Total Jobs ||<pre>Total Jobs=
CALCULATE(
    DISTINCTCOUNT('Quotation'[id]),
    FILTER(
        'Quotation',
        NOT ISBLANK('Quotation'[id])
    )
)</pre>|| The formula calculates the total number of unique jobs by quotation ID
|-
|-
| TBC ||<pre>TBC</pre>|| TBC
| Total Booked Customers ||<pre>Booked Customers =
COALESCE(
CALCULATE(
    DISTINCTCOUNT('Quotation'[customer_id]),
    FILTER(
        'Quotation',
        NOT ISBLANK('Quotation'[customer_id]) &&
        (
            NOT ISBLANK('Quotation'[booked_at]) ||
            NOT ISBLANK('Quotation'[confirmed_at])
        ) &&
        ISBLANK('Quotation'[lost_at]) &&
        ISBLANK('Quotation'[deleted_at])
    )
),0)</pre>|| The formula calculates the total number of booked customers by customer id
|-
| Total Chargeable Weights in kg ||<pre>Chargeable_Weight quoted kg =
COALESCE(
CALCULATE(
    SUM(Quotation[chargeable_weight_kg]),
    NOT ISBLANK(Quotation[quoted_at]),
    Quotation[type]=8),0)</pre>|| The formula calculates the total chargeable weight in kilograms
|-
| Total Revenue in euros booked or confirmed ||<pre>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)</pre>|| 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 ||<pre>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)</pre>|| The formula calculates the gross profit in euros where either the booked timestamp or the confirmed timestamp is not null.
|-
| Total Cost in euros ||<pre>Total Cost (EUR booked or confirmed) =
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_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_cost_total]
            / 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate],
        0
    )
)</pre>|| The formula calculates the total cost in euros where either the booked timestamp or the confirmed timestamp is not null.
|-
| Total Margin in euros % ||<pre>Margin (%) =
DIVIDE(
    [Total Revenue (EUR Booked or Confirmed)] - [Total Cost (EUR booked or confirmed)],
    [Total Revenue (EUR Booked or Confirmed)],
    0
) </pre>|| The formula calculates the total margin in euros where either the booked timestamp or the confirmed timestamp is not null.
|-
| Origin destination
pair
|<pre> each [origin] & " - " & [destination]</pre>|| The formula concatenate origin column and destination column
 
|}
|}

Revision as of 12:43, 26 November 2025

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 Jobs 
Total Jobs= 
CALCULATE(
    DISTINCTCOUNT('Quotation'[id]),
    FILTER(
        'Quotation',
        NOT ISBLANK('Quotation'[id])
    )
)
 The formula calculates the total number of unique jobs by quotation ID
Total Booked Customers 
Booked Customers = 
COALESCE(
CALCULATE(
    DISTINCTCOUNT('Quotation'[customer_id]),
    FILTER(
        'Quotation',
        NOT ISBLANK('Quotation'[customer_id]) &&
        (
            NOT ISBLANK('Quotation'[booked_at]) ||
            NOT ISBLANK('Quotation'[confirmed_at])
        ) &&
        ISBLANK('Quotation'[lost_at]) &&
        ISBLANK('Quotation'[deleted_at])
    )
),0)
 The formula calculates the total number of booked customers by customer id
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 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 Cost in euros 
Total Cost (EUR booked or confirmed) = 
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_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_cost_total] 
            / 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate],
        0
    )
)
 The formula calculates the total cost in euros where either the booked timestamp or the confirmed timestamp is not null.
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.
Origin destination

pair 

 each [origin] & " - " & [destination]
 The formula concatenate origin column and destination column
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Detail_(W%2BM)&oldid=353"