Quote & Bookings Summary Dashboard - Revision history

David.weller: /* Data Import Calculations */

2025-07-25T13:11:08Z

Data Import Calculations

← Older revision		Revision as of 15:11, 25 July 2025
Line 24:		Line 24:
	\|}		\|}

	== Data ~~Import Calculations~~ ==		== Data Transformation Applied ==
	[To be added]		[To be added]
	{\| class="wikitable"		{\| class="wikitable"
	\|+		\|+
	!~~Calculation~~		!Query
			!Calculations
	!Description		!Description
	\|-		\|-
			\|
	\|		\|
	\|		\|

David.weller at 10:45, 3 July 2025

2025-07-03T10:45:48Z

@@ Line 22: / Line 22: @@
 |-
 | Date Range || Displays only jobs created within the specified date range.
 |}

Stanley.songok at 10:05, 16 June 2025

2025-06-16T10:05:23Z

@@ Line 287: / Line 287: @@
   NOT (ISBLANK (quotations[quoted_at])))  </pre> || This clustered column chart provides a detailed breakdown of total quotes categorized by loss reasons.  Identify the most frequent reasons for losing potential bookings.
 |-
-| 15 || || The clustered bar chart provides a detailed breakdown of Total Bookings categorized by Customer.
+| 15. Total Bookings By Customer ||<pre>Total Bookings =
 |}

Stanley.songok at 10:03, 16 June 2025

2025-06-16T10:03:06Z

← Older revision		Revision as of 12:03, 16 June 2025
Line 259:		Line 259:
	)  </pre> \|\| The clustered bar chart breakdown total booking by Service.		)  </pre> \|\| The clustered bar chart breakdown total booking by Service.
	\|-		\|-
	\| 13 WeightBreakChar\|\| <pre>WeightBreakChar =		\| 13. WeightBreakChar\|\| <pre>WeightBreakChar =
	SWITCH(		SWITCH(
	TRUE(),		TRUE(),
Line 277:		Line 277:
	*This segmentation helps in understanding which weight categories have the highest conversion rates and booking volumes. [Calculation goes here]		*This segmentation helps in understanding which weight categories have the highest conversion rates and booking volumes. [Calculation goes here]
	\|-		\|-
	\| 14\|\| \|\| This clustered column chart provides a detailed breakdown of total quotes categorized by loss reasons. Identify the most frequent reasons for losing potential bookings.		\| 14. Total Qoutes By Loss Reasons\|\| <pre>Total Quotes =

			CALCULATE (

			COUNTROWS (quotations),

			USERELATIONSHIP (DimDate [Date], quotations [quoted at]),

			NOT (ISBLANK (quotations[quoted_at])))  </pre> \|\| This clustered column chart provides a detailed breakdown of total quotes categorized by loss reasons. Identify the most frequent reasons for losing potential bookings.
	\|-		\|-
	\| 15 \|\| \|\| The clustered bar chart provides a detailed breakdown of Total Bookings categorized by Customer.		\| 15 \|\| \|\| The clustered bar chart provides a detailed breakdown of Total Bookings categorized by Customer.
	\|}		\|}

Stanley.songok at 09:58, 16 June 2025

2025-06-16T09:58:11Z

@@ Line 259: / Line 259: @@
 )  </pre> ||   The clustered bar chart breakdown total booking by Service.
 |-
-| 13 || ||  Breakdown by Weight Breaks in Chargeable Weight:
+| 13 WeightBreakChar|| <pre>WeightBreakChar =
 *The table segments the data based on different weight break categories in chargeable weight.

Stanley.songok: /* Calculations */

2025-06-16T07:58:54Z

Calculations

← Older revision		Revision as of 09:58, 16 June 2025
Line 247:		Line 247:
	\|\| This measure calculates the conversion rate for SpotRate, which is the ratio of Won bookings to Total quotations.		\|\| This measure calculates the conversion rate for SpotRate, which is the ratio of Won bookings to Total quotations.
	\|-		\|-
	\| 12\|\| \|\| The clustered bar chart breakdown total booking by Service.		\| 12. Total Bookings By service\|\|<pre>Total Bookings =

			CALCULATE (

			COUNTROWS (quotations),

			NOT (ISBLANK (quotations [booked_at])),

			USERELATIONSHIP (quotations [booked_at], DimDate [Date])

			)  </pre> \|\| The clustered bar chart breakdown total booking by Service.
	\|-		\|-
	\| 13 \|\| \|\| Breakdown by Weight Breaks in Chargeable Weight:		\| 13 \|\| \|\| Breakdown by Weight Breaks in Chargeable Weight:

Stanley.songok at 11:56, 13 June 2025

2025-06-13T11:56:33Z

← Older revision		Revision as of 13:56, 13 June 2025
Line 98:		Line 98:
	The formula ensures that a booking is only counted if:		The formula ensures that a booking is only counted if:

	The quotation was either quoted (quoted_at) or pre-quoted (pre_quoted_at).		*The quotation was either quoted (quoted_at) or pre-quoted (pre_quoted_at).

	The quotation was successfully converted into a booking (booked_at).		*The quotation was successfully converted into a booking (booked_at).

	\|-		\|-

Stanley.songok at 11:55, 13 June 2025

2025-06-13T11:55:22Z

← Older revision		Revision as of 13:55, 13 June 2025
Line 93:		Line 93:
	(NOT (ISBLANK (quotations[quoted_at])) \|\| NOT (ISBLANK (quotations[pre_quoted_at]))) &&		(NOT (ISBLANK (quotations[quoted_at])) \|\| NOT (ISBLANK (quotations[pre_quoted_at]))) &&

	NOT (ISBLANK (quotations[booked_at])))  </pre>		NOT (ISBLANK (quotations[booked_at])))  </pre>
			\|\| The Converted Bookings formula calculates the total number of quotations that successfully converted into bookings:

			The formula ensures that a booking is only counted if:

			The quotation was either quoted (quoted_at) or pre-quoted (pre_quoted_at).

			The quotation was successfully converted into a booking (booked_at).

	\|-		\|-

David.weller: /* Calculations */

2025-06-13T09:08:31Z

Calculations

@@ Line 134: / Line 134: @@
 |-
 | 8 -
-i. CW booking
+|| i. CW booking <pre>CW_Booking =
 CALCULATE (
@@ Line 143: / Line 142: @@
     NOT (ISBLANK (quotations[booked_at])),
-    USERELATIONSHIP (DimDate [Date], quotations[booked_at]))
+    USERELATIONSHIP (DimDate [Date], quotations[booked_at]))</pre>
   ||
 *Sum of chargeable weight: It sums the values from the chargeable_weight column in the quotations table, representing the total weight of items for bookings.
@@ Line 155: / Line 189: @@
 This measure helps you track the total chargeable weight for all bookings within a specific date range.
 ii. GW Booking
 * Sum of gross weight: It sums the values from the gross_weight column in the quotations table, representing the total weight of items for bookings, including any packaging or additional weight.
@@ Line 180: / Line 199: @@
 This measure helps track the total gross weight of all bookings within a specific date range.
-           CALCULATE (
+iii. CW Quote
 *Sum of chargeable weight: It sums the values from the chargeable_weight column in the quotations table, representing the total chargeable weight for the items being quoted.
@@ Line 202: / Line 210: @@
 This measure helps you track the total chargeable weight for all quotations within a specific date range.
-                  CALCULATE (
+iv. GW Quote
 *Sum of gross weight: It sums the values from the gross_weight column in the quotations table, representing the total weight of items in the quotations, including packaging and any additional weight.
@@ Line 223: / Line 220: @@
 Purpose
 This measure helps track the total gross weight for all quotations within a specific date range.
 |-
-|9 - Total   || Total = [Won] + [Lost] + [Pending]
+|9 - Total   ||<pre> Total = [Won] + [Lost] + [Pending] </pre>
 || The Total measure calculates the total count of quotations based on the sum of Won, Lost, and Pending quotations.
 |-
-| 10 - Won || Won =
+| 10 - Won ||<pre> Won =
 CALCULATE (
@@ Line 236: / Line 234: @@
     quotations[spotrate] = 1 && quotations [Booking_categories] = "Direct Booking"
+)   </pre>
 || This measure counts the number of "Direct Bookings" where the SpotRate is 1, indicating a successful booking conversion.
 |-
-| 11 - Conversion Rate || Conversion Rate(spotrate) = DIVIDE([Won], [Total],0)
+| 11 - Conversion Rate || <pre> Conversion Rate(spotrate) = DIVIDE([Won], [Total],0) </pre>
   ||   This measure calculates the conversion rate for SpotRate, which is the ratio of  Won bookings to Total quotations.
 |-

David.weller at 09:00, 13 June 2025

2025-06-13T09:00:26Z

← Older revision		Revision as of 11:00, 13 June 2025
Line 38:		Line 38:
	* Ensuring only valid prequotes with a date are included.		* Ensuring only valid prequotes with a date are included.
	\|-		\|-
	\| 2 - Total Quotes \|\| Total Quotes = CALCULATE(COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[quoted_at]), NOT(ISBLANK(quotations[quoted_at])))		\| 2 - Total Quotes \|\| <pre>Total Quotes = CALCULATE(COUNTROWS(quotations), USERELATIONSHIP(DimDate[Date], quotations[quoted_at]), NOT(ISBLANK(quotations[quoted_at])))</pre>
	\|\| This measure counts the total number of finalized quotes created. Here's how it works:		\|\| This measure counts the total number of finalized quotes created. Here's how it works:

Line 47:		Line 47:
	\|-		\|-
	\|3 -Total Bookings \|\|		\|3 -Total Bookings \|\|
	Total Bookings =		<pre>Total Bookings =

	CALCULATE (		CALCULATE (
Line 55:		Line 55:
	NOT (ISBLANK (quotations [booked_at])),		NOT (ISBLANK (quotations [booked_at])),

	USERELATIONSHIP (quotations [booked_at], DimDate [Date]))		USERELATIONSHIP (quotations [booked_at], DimDate [Date]))</pre>
	\|\| This measure counts the total number of bookings made. Here's how it works:		\|\| This measure counts the total number of bookings made. Here's how it works:

Line 64:		Line 64:
	*Counts the rows: It counts all the rows where a valid booking date is present, giving the total number of bookings.		*Counts the rows: It counts all the rows where a valid booking date is present, giving the total number of bookings.
	\|-		\|-
	\|4 - Confirmed Bookings \|\| Confirmed Bookings =		\|4 - Confirmed Bookings \|\| <pre>Confirmed Bookings =

	CALCULATE (		CALCULATE (
Line 72:		Line 72:
	NOT (ISBLANK (quotations [confirmed at])),		NOT (ISBLANK (quotations [confirmed at])),

	USERELATIONSHIP (DimDate [Date], quotations [confirmed at]))		USERELATIONSHIP (DimDate [Date], quotations [confirmed at])) </pre>
	\|\| This measure counts the total number of bookings that have been confirmed. Here's how it works:		\|\| This measure counts the total number of bookings that have been confirmed. Here's how it works:

Line 83:		Line 83:
	*This measure helps track the number of bookings that have been officially finalized and confirmed.		*This measure helps track the number of bookings that have been officially finalized and confirmed.
	\|-		\|-
	\|5 - Converted Bookings \|\| Converted_Bookings =		\|5 - Converted Bookings \|\| <pre>Converted_Bookings =

	CALCULATE (		CALCULATE (
Line 93:		Line 93:
	(NOT (ISBLANK (quotations[quoted_at])) \|\| NOT (ISBLANK (quotations[pre_quoted_at]))) &&		(NOT (ISBLANK (quotations[quoted_at])) \|\| NOT (ISBLANK (quotations[pre_quoted_at]))) &&

	NOT (ISBLANK (quotations[booked_at])))		NOT (ISBLANK (quotations[booked_at])))  </pre>

	\|-		\|-
	\|6 - Direct Booking \|\| Direct_Booking =		\|6 - Direct Booking \|\| <pre>Direct_Booking =

	CALCULATE (		CALCULATE (
Line 108:		Line 108:
	ISBLANK (quotations[pre_quoted_at]),		ISBLANK (quotations[pre_quoted_at]),

	NOT (ISBLANK (quotations[booked_at])))		NOT (ISBLANK (quotations[booked_at]))) </pre>
	\|\| This measure counts the total number of bookings that were made without going through a quote or prequote process. Here's how it works:		\|\| This measure counts the total number of bookings that were made without going through a quote or prequote process. Here's how it works:

Line 121:		Line 121:
	*Counts the rows: After applying these conditions, it counts the rows that meet the criteria. .		*Counts the rows: After applying these conditions, it counts the rows that meet the criteria. .
	\|-		\|-
	\| 7 - Conversion Rate \|\| Conversion Rate = DIVIDE([Converted_Bookings], [Total_Quotes],0)  \|\| This measure calculates the conversion rate, which shows the percentage of quotes that were successfully converted into bookings. Here's how it works:		\| 7 - Conversion Rate \|\| <pre>Conversion Rate = DIVIDE([Converted_Bookings], [Total_Quotes],0) </pre> \|\| This measure calculates the conversion rate, which shows the percentage of quotes that were successfully converted into bookings. Here's how it works:

	*Numerator - Converted Bookings: It takes the total number of Converted Bookings, which are bookings that originated from quotes or prequotes and were successfully booked.		*Numerator - Converted Bookings: It takes the total number of Converted Bookings, which are bookings that originated from quotes or prequotes and were successfully booked.