Issue with Percentage Calculation in Power BI Using Databricks as Source

[Richie1602]

Hi everyone,

I've created a financial summary report in Power BI, and my source is Databricks. I have created a view for each financial metric name along with the calculations. All my amount fields are accurate, but when calculating percentages, I’m getting divide-by-zero errors. I addressed this issue using Power BI, and it worked initially. but if I sort the metrics by the MetricNo column, all my custom percentages become 0, and I am unable to figure out why.

I have tried handling this in Databricks using null if to ignore zeros, which gave me slightly varied percentages which is expected and I want to continue with Databricks, Is there any way I could achieve this with Databricks?

[Walter_C]

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:

1. NULLIF(TotalAmount, 0) returns NULL if TotalAmount is zero, avoiding the divide-by-zero error
2. If the division results in NULL (due to a zero denominator), COALESCE will return 0 as the percentage

[Richie1602]

Thanks, Walter for replying, I have tried NULLIF and the nulls are being handled at Power BI. Even if I tried Coalesce the value difference is still not quiet accurate. One of metrics is Gross Profiit, my amounts are Sales - COGS and Percent is (Sales - COGS)/Sales. Because of other dimensions in my query in some rows Sales is 0 but COGS has values and but in visual it will be a different dimension and needs to match with sales and COGS accurately for mutliple customers