Others

How to Allocate Cost per Unit Across Multiple Fact Tables in Power BI DAX

Steve K Oct 22, 2026 868 views

Question details

The user needs to accurately allocate daily expenses across production units in a Power BI data model containing multiple fact tables, while respecting Date and Size filter contexts.

Power BI DAX Cost per Unit Allocation Across Multiple Fact Tables

Product: Power BI
Device & OS: not provided
Scenario: Calculating a reliable cost per unit using DAX when expenses and production data reside in separate fact tables.
Observed behavior: The goal is to calculate cost per unit by dividing total expenses by total production units without using the ALL function, which unexpectedly removes necessary table filters.

Before you start

Ensure that your Power BI data model has active, correctly configured one-to-many relationships connecting your shared dimension tables (such as Date and Size) to both of your fact tables.

Solution 1Recommended

Create Base Measures and Calculate Cost per Unit

Use basic SUM measures combined with the DAX DIVIDE function to allocate costs while properly maintaining the filter context from your dimension tables.

Applying the ALL function to an entire fact table can clear out necessary dimension filters, causing inaccurate results. Instead, it is safer to aggregate the totals using standard SUM measures and then divide them safely.

Create the Total Units measure

In Power BI Desktop, right-click the Production table, select 'New measure', and enter: Total Units = SUM(Production[Total]).

Create the Total Expenses measure

Right-click your Expenses table, select 'New measure', and enter: Total Expenses = SUM(Expenses[Payment]).

Calculate Cost per Unit

Create a third measure to perform the allocation by entering: Cost per Unit = DIVIDE([Total Expenses], [Total Units], 0).

Validate the measures

Add a Matrix visual to your report canvas. Place your Date or Size dimensions in the rows, and add all three measures to the values area to verify that the filter contexts are respected and the totals are correct.

Create Base Measures and Calculate Cost per Unit

Safe Division: Using the DIVIDE function with '0' as the third argument safely handles division by zero, preventing infinite values or errors from breaking your visuals.

Free Microsoft Office alternative

Perform Powerful Data Analysis with WPS Spreadsheet

If you prefer handling cost allocations and data modeling in a familiar spreadsheet environment without complex DAX formulas, WPS Office provides a robust, lightweight alternative to Microsoft Office. With powerful Pivot Tables and advanced formulas, you can easily summarize and allocate costs.

1. Open your data file: Launch WPS Spreadsheet and open your raw data exported from your database or ERP.
2. Insert a Pivot Table: Highlight your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Summarize Costs: Drag your date and size dimensions to the Rows area, and place your expenses and production units into the Values area to instantly allocate costs.

Highly compatible with Microsoft Excel (.xlsx) formats and standard spreadsheet formulas.Features advanced Pivot Tables for instant cost allocation across multiple dimensions.Free to use, lightweight, and installs in seconds.Familiar user interface requires no steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use the ALL function for the Production table in this scenario?

Using ALL(Production) completely removes all filters applied to the Production table. This means any active slicers or visual row headers for Date or Size will be ignored, resulting in a flat, inaccurate cost allocation instead of daily or category-specific breakdowns.

How do I resolve missing relationship issues between fact tables in Power BI?

You should not create direct many-to-many relationships between two fact tables like Expenses and Production. Instead, use a star schema design by creating centralized dimension tables (like a shared Date table) and connecting them to both fact tables with one-to-many relationships.

What is the benefit of using the DIVIDE function over the standard slash (/) operator?

The DIVIDE function in DAX is designed to automatically handle division by zero errors. If your total units for a specific date are blank or zero, DIVIDE will return the specified alternate result (like 0) rather than displaying an error message in your report visual.

Office Quick Solutions