Revenue & Booking Overview: Difference between revisions
No edit summary |
No edit summary |
||
| (5 intermediate revisions by the same user not shown) | |||
| Line 47: | Line 47: | ||
|- | |- | ||
|1. Total Revenue | |1. Total Revenue | ||
| | |CALCULATE( | ||
| | SUM(quotations[total_cost]), | ||
quotations[Total_Bookings_Confirmed] = "Confirmed Booking" | |||
) + 0 | |||
|Calculates and formats total confirmed booking costs by currency, excluding currencies with zero revenue. The measure aligns amounts neatly by padding values with spaces and outputs a list showing each total alongside its corresponding currency, separated by line breaks for clear display. | |||
|- | |- | ||
|2. Total Bookings(Confirmed) | |2. Total Bookings(Confirmed) | ||
| | |CALCULATE( | ||
| | COUNTROWS(quotations), | ||
NOT(ISBLANK(quotations[booked_at])), | |||
NOT(ISBLANK(quotations[confirmed_at])), | |||
USERELATIONSHIP(quotations[booked_at], DimDate[Date]) | |||
) | |||
|Counts '''all bookings''' that have both a '''booking date''' and a '''confirmation date''', using the booking date as the active relationship to provide accurate time-based booking totals. | |||
|- | |- | ||
|3. Total Direct Bookings(Confirmed) | |3. Total Direct Bookings(Confirmed) | ||
| | |CALCULATE( | ||
| | COUNTROWS(quotations), | ||
USERELATIONSHIP(DimDate[Date], quotations[booked_at]), | |||
ISBLANK(quotations[quoted_at]), | |||
( | |||
(NOT(ISBLANK(quotations[booked_at])) && NOT(ISBLANK(quotations[confirmed_at]))) | |||
|| | |||
(NOT(ISBLANK(quotations[pre_quoted_at])) && NOT(ISBLANK(quotations[booked_at])) && NOT(ISBLANK(quotations[confirmed_at]))) | |||
) | |||
) | |||
|Counts '''all bookings''' that bypassed the standard quotation stage or originated from '''pre-quotes''', ensuring they are b'''oth booked and confirmed'''. This measure uses the booking date for accurate time-based tracking. | |||
|- | |- | ||
|4. Total Converted Booking(Confirmed) | |4. Total Converted Booking(Confirmed) | ||
| | |CALCULATE( | ||
| | COUNTROWS(quotations), | ||
USERELATIONSHIP(DimDate[Date], quotations[booked_at]), | |||
NOT(ISBLANK(quotations[quoted_at])) && | |||
NOT(ISBLANK(quotations[booked_at])) && | |||
NOT(ISBLANK(quotations[confirmed_at])) | |||
) | |||
|This measure counts the number of quotations that have progressed through all key stages—'''quoted, booked, and confirmed'''—using the booking date as the active relationship for time-based analysis. It reflects only fully converted bookings. | |||
|- | |- | ||
|5. Total Chargeable Weight | |5. Total Chargeable Weight | ||
| | |'''Chargeable Weight'''- > Sourced directly from the quotations table. | ||
| | |This metric captures the '''chargeable weight''' exactly as recorded in the quotations table, ensuring accuracy by using the original shipment data without additional calculations or transformations | ||
|- | |- | ||
|6. Top Customers By Revenue | |6. Top Customers By Revenue | ||
|Aggregates total revenue from confirmed bookings and groups it by customer to compare side by side in a clustered bar chart and with specific currency used for each. | |Aggregates '''total revenue''' from '''confirmed bookings''' and groups it by '''customer''' to compare side by side in a clustered bar chart and with specific currency used for each. | ||
|This chart displays the highest revenue-generating customers, showing amounts in their respective currencies. | |This chart displays the highest '''revenue-generating customers''', showing amounts in their respective currencies. | ||
|- | |- | ||
|7. Top Bookings (Confirmed) by Origin | |7. Top Bookings (Confirmed) by Origin | ||
|Counts only confirmed bookings (converted quotes that have been finalized as bookings), then groups them by origin to compare side by side in a clustered bar chart. | |Counts only '''confirmed bookings''' (converted quotes that have been finalized as bookings), then groups them by '''origin''' to compare side by side in a clustered bar chart. | ||
|This chart shows the total confirmed bookings for each origin location, making it easy to identify which origins generate the highest booking volumes and highlight performance differences across locations. | |This chart shows the '''total confirmed bookings''' for each '''origin''' location, making it easy to identify which origins generate the highest booking volumes and highlight performance differences across locations. | ||
|- | |- | ||
|8. Top Bookings(Confirmed) by Destination | |8. Top Bookings(Confirmed) by Destination | ||
|Counts only confirmed bookings (converted quotes that have been finalized as bookings), then groups them by destination to compare side by side in a clustered bar chart. | |Counts only '''confirmed bookings''' (converted quotes that have been finalized as bookings), then groups them by '''destination''' to compare side by side in a clustered bar chart. | ||
|This chart shows the total confirmed bookings for each destination location, making it easy to identify which destinations receive the highest booking volumes and highlight performance differences across locations. | |This chart shows the total '''confirmed bookings''' for each '''destination''' location, making it easy to identify which destinations receive the highest booking volumes and highlight performance differences across locations. | ||
|- | |- | ||
|9. Top Bookings by Service | |9. Top Bookings by Service | ||
|Counts only confirmed bookings and groups them by service type to compare in a clustered bar chart. | |Counts only '''confirmed bookings''' and groups them by '''service''' type to compare in a clustered bar chart. | ||
|This chart highlights the total confirmed bookings for each service type, making it easy to see which services are most in demand and where performance differences exist across offerings. | |This chart highlights the '''total confirmed bookings''' for each '''service''' type, making it easy to see which services are most in demand and where performance differences exist across offerings. | ||
|- | |- | ||
|10. Bookings by Weight Breaks Matrix Table | |10. Bookings by Weight Breaks Matrix Table | ||
| | |Groups bookings into predefined '''weight break'''s and displays three key measures side by side: '''Total Bookings, Direct Bookings, and Converted Bookings,''' allowing detailed comparison within each weight category. | ||
| | |This matrix table provides a clear breakdown of '''booking volumes by weight segment'''. | ||
|- | |- | ||
|11.Conversion Rate by Month | |11.Conversion Rate by Month | ||
| | |Calculates the percentage of quotes c'''onverted into confirmed bookings''' for each month, grouping results over time to show conversion trends. | ||
| | |This chart highlights how effectively quotes are turning into bookings month by month. | ||
|- | |- | ||
|12. Total Monthly Revenue | |12. Total Monthly Revenue | ||
|Aggregates total revenue from confirmed bookings and groups it by month to show revenue trends over time, plus the respective currencies. | |Aggregates '''total revenue''' from '''confirmed bookings''' and groups it by month to show revenue trends over time, plus the '''respective currencies'''. | ||
|This chart provides a clear view of | |This chart provides a clear view of m'''onthly revenue performance''', showing amounts in their original currencies. | ||
|- | |- | ||
|13. Total Chargeable Weight by Customer | |13. Total Chargeable Weight by Customer | ||
| | |Aggregates the '''total chargeable weight''' and groups it by customer to compare side by side in a clustered bar chart. | ||
| | |This chart displays the '''total chargeable weight''' attributed to each customer. | ||
|} | |} | ||
Latest revision as of 11:02, 21 August 2025
Summary
Filters
| Filter | Description |
|---|---|
| Carrier | Select to filter the Quote page by Carrier Name |
| Customer | Select to filter the Quote page by Customer |
| Origin Airport | Select to filter the Quote page by Origin Airport |
| Destination Airport | Select to filter the Quote page by Destination Airport |
| Spot Rate Only | Select to filter the Quote page by either Spotrates or Non Spotrates |
| Hide Lost/Deleted | Select to filter the Quote page by choosing to Hide Lost & Deleted Quotes or show Lost & Deleted Quotes |
Data Transformation Applied
| Query | Transformations |
|---|---|
Calculations
| Columns | Calculation | Description |
|---|---|---|
| 1. Total Revenue | CALCULATE(
SUM(quotations[total_cost]), quotations[Total_Bookings_Confirmed] = "Confirmed Booking" ) + 0 |
Calculates and formats total confirmed booking costs by currency, excluding currencies with zero revenue. The measure aligns amounts neatly by padding values with spaces and outputs a list showing each total alongside its corresponding currency, separated by line breaks for clear display. |
| 2. Total Bookings(Confirmed) | CALCULATE(
COUNTROWS(quotations), NOT(ISBLANK(quotations[booked_at])), NOT(ISBLANK(quotations[confirmed_at])), USERELATIONSHIP(quotations[booked_at], DimDate[Date]) ) |
Counts all bookings that have both a booking date and a confirmation date, using the booking date as the active relationship to provide accurate time-based booking totals. |
| 3. Total Direct Bookings(Confirmed) | CALCULATE(
COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[booked_at]), ISBLANK(quotations[quoted_at]), ( (NOT(ISBLANK(quotations[booked_at])) && NOT(ISBLANK(quotations[confirmed_at]))) || (NOT(ISBLANK(quotations[pre_quoted_at])) && NOT(ISBLANK(quotations[booked_at])) && NOT(ISBLANK(quotations[confirmed_at]))) ) ) |
Counts all bookings that bypassed the standard quotation stage or originated from pre-quotes, ensuring they are both booked and confirmed. This measure uses the booking date for accurate time-based tracking. |
| 4. Total Converted Booking(Confirmed) | CALCULATE(
COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[booked_at]), NOT(ISBLANK(quotations[quoted_at])) && NOT(ISBLANK(quotations[booked_at])) && NOT(ISBLANK(quotations[confirmed_at])) ) |
This measure counts the number of quotations that have progressed through all key stages—quoted, booked, and confirmed—using the booking date as the active relationship for time-based analysis. It reflects only fully converted bookings. |
| 5. Total Chargeable Weight | Chargeable Weight- > Sourced directly from the quotations table. | This metric captures the chargeable weight exactly as recorded in the quotations table, ensuring accuracy by using the original shipment data without additional calculations or transformations |
| 6. Top Customers By Revenue | Aggregates total revenue from confirmed bookings and groups it by customer to compare side by side in a clustered bar chart and with specific currency used for each. | This chart displays the highest revenue-generating customers, showing amounts in their respective currencies. |
| 7. Top Bookings (Confirmed) by Origin | Counts only confirmed bookings (converted quotes that have been finalized as bookings), then groups them by origin to compare side by side in a clustered bar chart. | This chart shows the total confirmed bookings for each origin location, making it easy to identify which origins generate the highest booking volumes and highlight performance differences across locations. |
| 8. Top Bookings(Confirmed) by Destination | Counts only confirmed bookings (converted quotes that have been finalized as bookings), then groups them by destination to compare side by side in a clustered bar chart. | This chart shows the total confirmed bookings for each destination location, making it easy to identify which destinations receive the highest booking volumes and highlight performance differences across locations. |
| 9. Top Bookings by Service | Counts only confirmed bookings and groups them by service type to compare in a clustered bar chart. | This chart highlights the total confirmed bookings for each service type, making it easy to see which services are most in demand and where performance differences exist across offerings. |
| 10. Bookings by Weight Breaks Matrix Table | Groups bookings into predefined weight breaks and displays three key measures side by side: Total Bookings, Direct Bookings, and Converted Bookings, allowing detailed comparison within each weight category. | This matrix table provides a clear breakdown of booking volumes by weight segment. |
| 11.Conversion Rate by Month | Calculates the percentage of quotes converted into confirmed bookings for each month, grouping results over time to show conversion trends. | This chart highlights how effectively quotes are turning into bookings month by month. |
| 12. Total Monthly Revenue | Aggregates total revenue from confirmed bookings and groups it by month to show revenue trends over time, plus the respective currencies. | This chart provides a clear view of monthly revenue performance, showing amounts in their original currencies. |
| 13. Total Chargeable Weight by Customer | Aggregates the total chargeable weight and groups it by customer to compare side by side in a clustered bar chart. | This chart displays the total chargeable weight attributed to each customer. |