Re: Issue with Percentage Calculation in Power BI ...

Richie1602 · ‎12-18-2024

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 · ‎12-18-2024

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

Richie1602 · ‎12-18-2024

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

Issue with Percentage Calculation in Power BI Using Databricks as Source