Hello Richie,
In Databricks, you can use a combination of NULLIF and COALESCE functions to handle divide-by-zero scenarios effectively. Here's an example of how you can modify your percentage calculation:
SELECT
MetricNo,
MetricName,
Amount,
COALESCE(
(Amount / NULLIF(TotalAmount, 0)) * 100,
0
) AS Percentage
FROM YourFinancialMetricsView
This approach works as follows:
NULLIF(TotalAmount, 0) returns NULL if TotalAmount is zero, avoiding the divide-by-zero error- If the division results in NULL (due to a zero denominator),
COALESCE will return 0 as the percentage
