Site Summary: Difference between revisions

Latest revision as of 13:41, 2 October 2025

Summary

The Site Summary page is part of the Yield, Weight, and Revenue report. It includes site currencies and displays data across various carriers.

Filters


Filter	Description
Database	Select the reporting database.
Site	Choose the location or branch for the report.
Carrier	Filter by transport provider.
Service	Narrow results by type of service offered.
Customer	View data for a specific customer.
Commodity Type	Filter by category of goods.
Product Type	Select specific product groups.
Date	Define the reporting period (weekly, monthly, or custom range).
Site Currency	Select specific currency of the revenue.

Data Transformation Applied


Query	Transformations
let Source = MySQL.Database( "qms-reporting.cluster-ro-cbuxcavwaolv.eu-west-2.rds.amazonaws.com", "db", [ ReturnSingleDatabase = true, CommandTimeout = #duration(0, 10, 0, 0),	Formatting – Data is arranged into a consistent structure used across all report pages. Merging – Related tables (e.g., customers, carriers, services) are combined so the report shows complete information in one place. Combining – Combined four tables (Groupair, AirCross, HAE, And ACS) Cleaning – Any extra technical fields are removed, and values are standardized for easy reading.

Calculations


Columns	Calculation	Description
1. Total Yield&Target Yield By Date	TotalYield Site = IF ( AND ( [Chargeable Weight] > 0, [Total Revenue Site] > 0 ), [Total Revenue Site] / [Chargeable Weight] ) Target Yield = SWITCH( TRUE(), SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "HAE" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "hae", 0.87, SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "HAE" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "alb", 2.92, SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "HAE" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "hai", 2.78, SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "HAE" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "us", 2.03, SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "HAE" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "nbo", 0.88, SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "HAE" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "dxb", 0.68, SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "HAE" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "scl", 1.76, SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "HAE" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "sao", 2.34, SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "AIRCROSS" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "AirCross", 1.00, -- placeholder SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "ACS" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "Ireland", 1.00, -- placeholder SELECTEDVALUE('Combined Quatations Tables2'[Database]) = "GROUPAIR" && SELECTEDVALUE('Combined Quatations Tables2'[Site]) = "GPA", 1.00, -- placeholder BLANK()	Total Yield Site – Calculates a site’s yield by dividing total revenue by chargeable weight (only when both are greater than zero). Target Yield – Uses preset target values for each site (based on the database and site name) to define the expected yield benchmark for comparison.
2. Total Revenue and Chargeable Weight By Date	CHW (KG) = SUMX ( 'Combined Quatations Tables2', IF ( 'Combined Quatations Tables2'[weight_unit] = "lb", 'Combined Quatations Tables2'[chargeable_weight] * 0.45359237, 'Combined Quatations Tables2'[chargeable_weight] -- already in KG ) ) Total Revenue Site = SUM('Combined Quatations Tables2'[Revenue2])	CHW (KG) – Calculates the total chargeable weight in kilograms, converting from Lb where needed. Total Revenue Site – Sums up all revenue values for the selected site.
3. Total Revenue By Carrier	Total Revenue Site = SUM('Combined Quatations Tables2'[Revenue2])	Total Revenue by Carrier shows the sum of revenue grouped by each carrier, allowing comparison of carrier performance.
4.TotalYield and Target Yield By Month Year	TotalYield Site = IF ( AND ( [Chargeable Weight] > 0, [Total Revenue Site] > 0 ), [Total Revenue Site] / [Chargeable Weight] )	Total Yield by Month/Year – Shows the actual yield (revenue ÷ chargeable weight) summarized for each month and year. Target Yield by Month/Year – Displays the preset yield targets for each site, organized by month and year, for comparison against actual yield.
5. Total Revenue & Chargeable Weight By Month	CHW (KG) = SUMX ( 'Combined Quatations Tables2', IF ( 'Combined Quatations Tables2'[weight_unit] = "lb", 'Combined Quatations Tables2'[chargeable_weight] * 0.45359237, 'Combined Quatations Tables2'[chargeable_weight] -- already in KG ) ) Total Revenue Site = SUM('Combined Quatations Tables2'[Revenue2])	Total Revenue by Month/Year – Shows the total revenue summarized for each month and year. Chargeable Weight by Month/Year – Displays the total chargeable weight summarized for each month and year.
6. Total Yield By Carrier	TotalYield Site = IF ( AND ( [Chargeable Weight] > 0, [Total Revenue Site] > 0 ), [Total Revenue Site] / [Chargeable Weight] )	Total Yield by Carrier – Shows the actual yield (revenue ÷ chargeable weight) summarized for each carrier, allowing performance comparison across carriers.
7.Table Display	Total Revenue Site = SUM('Combined Quatations Tables2'[Revenue2])	This table displays weekly figures, including week numbers, yield, total revenue, and chargeable weight. It provides a detailed breakdown of performance metrics across weeks for easier comparison and trend analysis.