Commitment value calculating incorrectly - DAX Logic

Nishesh — Tue, 05 May 2026 01:23:37 GMT

Issue with DAX Allocation Logic for Commitment Value with Split Funding & Cost Centres

Hi everyone,

I’m hoping someone can help me sanity‑check both my dataflow setup and my DAX logic, as I’m running into an allocation issue that I can’t fully explain.

Business Requirement

I need to calculate a Commitment Value for projects.

Normally, the commitment value comes from the Approved Budget.
However, when a project has Split Funding, the Approved Budget should be ignored, and instead the value should come from:
- Portion A
- Portion B

This part works correctly at a project level.

The Complication – Cost Centres

The problem arises because split-funded projects can span multiple cost centres, while my reporting is strictly done at the Cost Centre level.

Example:

One project
Two cost centres
Funding is split:
- Portion A → Cost Centre A
- Portion B → Cost Centre B

Data Model / Dataflow Setup

In the source data:

Portion A and Portion B each have their own cost centre columns
My report model only supports one Cost Centre key

To handle this:

In the dataflow, I merged the two cost centre columns into a single column called Reporting Cost Centre
This Reporting Cost Centre is what I use to create the relationship to the Cost Centre dimension table

This allows me to report at a single cost centre level, which is required for the report.

The Issue I’m Seeing

When using my current DAX:

The allocation is not correct
For split-funded projects, it appears that:
- Approved Budget is still being included
- Portion A (or B) is also being added
This results in the value being overstated, almost as if the amount is being double-counted

Specifically:

When filtering to a single cost centre, I expect:
- Only Portion A (or B) to be included
Instead, DAX appears to be:
- Adding Approved Budget + Portion A

Questions

Dataflow Design
Is merging the two cost centre columns into a single Reporting Cost Centre the correct approach, given that reporting must be done at only one cost centre level?
DAX Evaluation Logic
Why is DAX seemingly unable to follow the intended logic where:
- Approved Budget is ignored when split funding exists, and
- Only the portion relevant to the filtered cost centre is included?
Allocation Behavior
Is this behavior likely due to:
- The merged cost centre column,
- Filter context not propagating as expected, or
- A modeling issue that requires a different table structure (e.g. unpivoting Portion A / Portion B)?

Any guidance on whether this is a modeling issue vs. a DAX issue would be greatly appreciated.
Happy to share sample data or DAX if helpful.

Thanks in advance!

CPR Commitment Amount FINAL =

DIVIDE (

CALCULATE (

SUMX (

/* ------------------------------------------

Create correct row grain

------------------------------------------ */

ADDCOLUMNS (

FILTER (

UNION (

/* Row for Cost Centre (A) */

SELECTCOLUMNS (

CPR_MASTERDATA,

"ReportingCC", CPR_MASTERDATA[Cost Centre],

"SplitFlag", CPR_MASTERDATA[Is there a funding split _Y_],

"ApprovedBudget", CPR_MASTERDATA[Approved Budget],

"SplitPct", CPR_MASTERDATA[Portion Estimate % _A_],

"UpdPrel", CPR_MASTERDATA[Updated Preliminary Budget],

"StdPrel", CPR_MASTERDATA[Standard Preliminary Budget]

/* Row for Cost Centre (B) */

SELECTCOLUMNS (

CPR_MASTERDATA,

"ReportingCC", CPR_MASTERDATA[Cost Centre _B_],

"SplitFlag", CPR_MASTERDATA[Is there a funding split _Y_],

"ApprovedBudget", CPR_MASTERDATA[Approved Budget],

"SplitPct", CPR_MASTERDATA[Portion Estimate % _B_],

"UpdPrel", CPR_MASTERDATA[Updated Preliminary Budget],

"StdPrel", CPR_MASTERDATA[Standard Preliminary Budget]

)

NOT ISBLANK ( [ReportingCC] )

"AllocatedAmount",

SWITCH (

TRUE (),

/* ✅ SPLIT PROJECTS */

[SplitFlag] = "Y"

&& [ApprovedBudget] <> 0,

[ApprovedBudget] * [SplitPct],

/* Split projects – no Approved Budget */

[SplitFlag] = "Y"

&& [ApprovedBudget] = 0

&& [UpdPrel] <> 0,

[UpdPrel],

[SplitFlag] = "Y"

&& [ApprovedBudget] = 0

&& [UpdPrel] = 0,

[StdPrel],

/* ✅ SINGLE COST CENTRE */

[SplitFlag] <> "Y"

&& [ApprovedBudget] <> 0,

[ApprovedBudget],

[SplitFlag] <> "Y"

&& [ApprovedBudget] = 0

&& [UpdPrel] <> 0,

[UpdPrel],

[SplitFlag] <> "Y"

&& [ApprovedBudget] = 0

&& [UpdPrel] = 0,

[StdPrel],

)

[AllocatedAmount]

/* Commence Year exclusion */

NOT (

CPR_MASTERDATA[Commence Year]

IN {

"2015/16",

"2016/17",

"2017/18",

"2018/19",

"2019/20",

"2020/21"

}

)

1000,

)

Approved Budget	Cost Centre	Is there a funding split (Y)	Portion Estimate % (A)	Portion Estimate % (B)	Approved Portion $ (A)	Approved Portion $ (B)	Funding Program Name (B)	Cost Centre (B)
$14,275,975.00	3135025	Yes	93%	7%	13,454,970.00	821,005.00	ABC Business Group	3182567

SINGLE COST CENTRE

Committed Funding

Column

Priority

Cost Centre

Approved Budget

Always use if it contains a value other than 0

The relevant Cost Centre will be in the column titled "Cost Centre"

Updated Preliminary Budget

Use if "Approved Budget" value is 0 or blank.

Standard Preliminary Budget

Use if "Approved Budget" and "Updated Preliminary Budget" are both either 0 or blank.

MULTIPLE COST CENTRE

Committed Funding

Column

Priority

Cost Centre

Approved Portion $ (A)

Approved Portion $ (B)

Always use if it contains a value other than 0.
The combined value of the Portions should always add to the "Approved Budget".

The relevant Cost Centre for Portion A will be in the column titled "Cost Centre" the relevant Cost Centre for Portion B will be in the column titled "Cost Centre (B)".

Updated Preliminary Budget

Use if "Approved Budget" value is 0 or blank.

The relevant Cost Centre will be in the column titled "Cost Centre"

Standard Preliminary Budget

Use if "Approved Budget" and "Updated Preliminary Budget" are both either 0 or blank.

Re: Commitment value calculating incorrectly - DAX Logic

lbendlin — Thu, 07 May 2026 12:23:28 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Commitment value calculating incorrectly - DAX Logic

Nishesh — Fri, 08 May 2026 02:11:08 GMT

I will want to close this one as i have already found a solution.

Best appraoch was to do the querying within the dataset itself , and that has resolved the issue

Thanks

topic Re: Commitment value calculating incorrectly - DAX Logic in DAX Commands and Tips

Commitment value calculating incorrectly - DAX Logic

Issue with DAX Allocation Logic for Commitment Value with Split Funding & Cost Centres

Business Requirement

The Complication – Cost Centres

Data Model / Dataflow Setup

The Issue I’m Seeing

Questions

Re: Commitment value calculating incorrectly - DAX Logic

Re: Commitment value calculating incorrectly - DAX Logic