Quotes: Difference between revisions

Latest revision as of 12:28, 20 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
Total Quotes	Total Quotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[quoted_at]), NOT(ISBLANK(quotations[quoted_at])) )	Counts all quotations based on quoted_at, using USERELATIONSHIP to activate the correct date link with DimDate[Date].
2. Converted Quotes	Converted Quotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[booked_at]), // Temporarily activate the relationship with booked_at (NOT(ISBLANK(quotations[quoted_at])) && NOT(ISBLANK(quotations[booked_at])) ))	Counts quotations that have both quoted_at and booked_at, using USERELATIONSHIP to activate the date link on booked_at for accurate filtering.
3. Total Lost & Deleted	Total_Lost_Deleted_Formatted = VAR TotalLost = CALCULATE ( COUNTROWS ( Quotations ), USERELATIONSHIP ( DimDate[Date], Quotations[quoted_at] ), NOT ( ISBLANK ( Quotations[quoted_at] ) ), -- Was quoted NOT ( ISBLANK ( Quotations[lost_at] ) ) -- Marked as lost ) + 0 VAR TotalDeleted = CALCULATE ( COUNTROWS ( Quotations ), USERELATIONSHIP ( DimDate[Date], Quotations[quoted_at] ), NOT ( ISBLANK ( Quotations[quoted_at] ) ), -- Was quoted NOT ( ISBLANK ( Quotations[deleted_at] ) ), -- Marked as deleted ISBLANK ( Quotations[lost_at] ) -- Not lost ) + 0 RETURN "Lost: " & FORMAT ( TotalLost, "#,0" ) & " \| Deleted: " & FORMAT ( TotalDeleted, "#,0" )	Calculates how many quotations were lost (quoted and have a lost_at date) and how many were only deleted (quoted, have deleted_at, but no lost_at). The result is returned as formatted text like: Lost: X \| Deleted: Y USERELATIONSHIP is used to activate the link between DimDate[Date] and quoted_at for accurate date filtering.
4. Total Spot Rate Quotes	Total Spot Rate Quotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[quoted_at]), FILTER( quotations, NOT(ISBLANK(quotations[quoted_at])) && quotations[spotrate] = 1 ) )	This measure counts all quotations where: quoted_at is not blank, spotrate equals 1, and it uses the inactive DimDate → quotations[quoted_at] relationship during calculation.
5. Total Quotes & Converted Quotes by Customer.	Uses existing DAX measures for Total Quotes and Converted Quotes and add them on the clustered bar chart.	This clustered bar chart shows each customer on the Y-axis and compares their total quotes against those converted quotes on the X axis.
6. Quotes & Converted quotes by Weight Break Table	WeightBreakChar classifies each quotation into weight break ranges (e.g., <45, <100, …, >=3000) based on chargeable_weight. Total Quotes counts quotations per weight break using the quoted_at date relationship. %Total Quotes divides each weight break’s quotations by the total quotations in the current selection. Converted Quotes counts successfully booked quotations per weight break using the booked_at date relationship. %Converted Quotes divides each weight break’s converted quotations by the total converted quotations in the current selection.	This table groups quotations by weight break (WB) and shows: Total Quotes per weight category, % of Total Quotes each weight break represents, Converted Quotes within each category, and % of Converted Quotes relative to the overall converted total.
7.Quotes by Weight Break and Service Name Matrix Table	The matrix counts the number of quotations using the quoted_at field and groups them by Weight Break and Service Name.	Shows how many quotations were created, organized by weight break and service name.
8. Top Tariffs By Total Quotes	Total Quotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[quoted_at]), NOT(ISBLANK(quotations[quoted_at]))) 2. Tariff Types = SWITCH( TRUE(), NOT ISBLANK(quotations[tariff_customer_id]) && quotations[spotrate] = 1, "Contract SR", NOT ISBLANK(quotations[tariff_customer_id]) && quotations[spotrate] <> 1, "Contract", quotations[tariff_flag_promo] = TRUE() && quotations[spotrate] = 1, "Promo SR", quotations[tariff_flag_promo] = TRUE() && quotations[spotrate] <> 1, "Promo", ISBLANK(quotations[tariff_customer_id]) && quotations[tariff_flag_promo] = FALSE() && quotations[spotrate] = 0, "Standard", "Spot Rate" )	Calculates Total Quotes using quotation date filtering (via USERELATIONSHIP) and classifies them into tariff types for visualization in a pie chart. Logic for classification: Contract SR – Customer contract exists (tariff_customer_id not blank) and spotrate = 1 Contract – Customer contract exists and spotrate ≠ 1 Promo SR – tariff_flag_promo = TRUE and spotrate = 1 Promo – tariff_flag_promo = TRUE and spotrate ≠ 1 Standard – No contract, no promo, and spotrate = 0 Spot Rate – All remaining cases (fallback) .
9. Top Tariffs By Converted Quotes	1.Converted Quotes = CALCULATE( COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[booked_at]), // Temporarily activate the relationship with booked_at (NOT(ISBLANK(quotations[quoted_at])) && NOT(ISBLANK(quotations[booked_at])) )) 2. Tariff Types = SWITCH( TRUE(), NOT ISBLANK(quotations[tariff_customer_id]) && quotations[spotrate] = 1, "Contract SR", NOT ISBLANK(quotations[tariff_customer_id]) && quotations[spotrate] <> 1, "Contract", quotations[tariff_flag_promo] = TRUE() && quotations[spotrate] = 1, "Promo SR", quotations[tariff_flag_promo] = TRUE() && quotations[spotrate] <> 1, "Promo", ISBLANK(quotations[tariff_customer_id]) && quotations[tariff_flag_promo] = FALSE() && quotations[spotrate] = 0, "Standard", "Spot Rate" )	Calculates Converted Quotes by counting quotations that were both quoted and booked (using USERELATIONSHIP on the booking date), then classifies them into tariff types for visualization in a pie chart. Logic for classification: Contract SR – Customer contract exists (tariff_customer_id not blank) and spotrate = 1 Contract – Customer contract exists and spotrate ≠ 1 Promo SR – tariff_flag_promo = TRUE and spotrate = 1 Promo – tariff_flag_promo = TRUE and spotrate ≠ 1 Standard – No contract, no promo, and spotrate = 0 Spot Rate – All remaining cases (fallback)
10.Total Quotes and Converted Quotes by Origin	Counts quotes and converted quotes , then groups them by origin to compare side by side in a clustered bar chart	This chart provides a side-by-side comparison of Total Quotes versus Converted Quotes for each origin location, making it easy to see which origins generate the highest quote volumes and where conversions are strongest or weakest.
11. Total Quotes and Converted Quotes by Destination	Counts quotes and converted quotes , then groups them by Destination to compare side by side in a clustered bar chart	This chart provides a side-by-side comparison of Total Quotes versus Converted Quotes for each Destination location, making it easy to see which origins generate the highest quote volumes and where conversions are strongest or weakest.
12. Total Quotes by Lost Reasons	Counts Total Quotes and groups them by loss reason, then displays them in a stacked column chart for easy comparison	This chart shows how quotes are distributed across different loss reasons, helping to identify the most common factors leading to lost quotes.
13. Total Quotes by Delete Reasons	Counts Total Quotes and groups them by Delete reason, then displays them in a stacked column chart for easy comparison	This chart shows how quotes are distributed across different Delete reasons, helping to identify the most common factors leading to deleted quotes.