Commitment value calculating incorrectly - DAX Logic

[Nishesh]

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

1. 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?

2. 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?

3. 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],

                        0

                    )

            ),

            [AllocatedAmount]

        ),

        /* Commence Year exclusion */

        NOT (

            CPR_MASTERDATA[Commence Year]

                IN {

                    "2015/16",

                    "2016/17",

                    "2017/18",

                    "2018/19",

                    "2019/20",

                    "2020/21"

                }

        )

    ),

    1000,

    0

)

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.

[lbendlin · Accepted solution]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Nishesh · Accepted solution]

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

[lbendlin · Accepted solution]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...