DIVIDE for safe division in Power BI - Dashboard Guide

Dashboard Mode - DIVIDE for safe division

Business Question

How can we calculate the average sales per order safely, avoiding errors when the number of orders is zero?

Sample Data

Region	Sales	Orders
North	1000	50
South	1500	0
East	2000	100
West	1200	60
Central	0	0

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUM(SalesData[Sales])
  Result: 5700
• KPI Card: Total Orders
  Formula: Total Orders = SUM(SalesData[Orders])
  Result: 210
• KPI Card: Average Sales per Order (Safe Division)
  Formula: Average Sales per Order = DIVIDE([Total Sales], [Total Orders], 0)
  Result: 27.14
• Bar Chart: Sales by Region
  X-axis: Region
  Y-axis: Sales
  Shows sales amounts for each region
• Table: Sales and Orders by Region with Average Sales per Order
  Columns: Region, Sales, Orders, Average Sales per Order
  Formula for Average Sales per Order per Region:
  DIVIDE(SUM(SalesData[Sales]), SUM(SalesData[Orders]), 0)
  Shows zero when orders are zero to avoid errors

Dashboard Layout

+----------------------+----------------------+----------------------+  
|   Total Sales (KPI)  |   Total Orders (KPI) | Average Sales/Order  |  
|        5700          |         210          |        27.14         |  
+----------------------+----------------------+----------------------+  
|                            Sales by Region Bar Chart                 |  
|                          (Regions on X, Sales on Y)                  |  
+---------------------------------------------------------------------+  
|          Sales and Orders Table with Average Sales per Order         |  
|  Region | Sales | Orders | Average Sales per Order (safe DIVIDE)       |  
+---------------------------------------------------------------------+  

Interactivity

A slicer filter on Region allows users to select one or more regions. When a region is selected:

• The KPI cards update to show totals and averages only for the selected regions.
• The bar chart updates to display sales for the selected regions.
• The table updates to show sales, orders, and average sales per order for the selected regions.

The DIVIDE function ensures that if any selected region has zero orders, the average sales per order calculation safely returns zero instead of an error.

Self Check

Try this: Add a filter to select only the South region where orders are zero.

Question: What happens to the Average Sales per Order KPI and the table values?

Answer: The average sales per order will show 0 safely without error because DIVIDE handles division by zero by returning the alternate result.

Key Result

Dashboard showing total sales, total orders, and safe average sales per order using DIVIDE to avoid division errors.