Issue with Percentage Calculation in Power BI Using Databricks as Source
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 01:01 PM
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 01:05 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2024 01:19 PM
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

