Problem with Grand Total of Measure in HASONEVALUE

[Bunmz_a]

Please I am experiencing a challenge with my DAX measure

My objective is to calculate the total budget associated with each Workorder status however for Projected Workorders, the budget for Open and Initiated should be netted off till it gets to 0. Meaning the Budget measure would need to incorporate an adjustment for Projected Workorders

I have been able to do this successfully but the grand total does not work.

For the grand total, I am still getting a sum that reflect the original not adjusted projected budget regardless of the logic built into the measure

Below is my measure. When I use ‘HASONEVALUE’ I get 0 as the grand total

Please help

Budget =

VAR WOstatus =

    FIRSTNONBLANK ( 'Projected WO'[WO_Status], 1 )

VAR BudgetTotal =

    CALCULATE (

        SUM ( Budget[Amount] ),

        FILTER ( Budget, Budget[BudgetType] = "Budget" )

    )

VAR ProjectedAmount =

    CALCULATE (

        BudgetTotal,

        'Projected WO'[WO_Status] IN { "Projected", "projected" }

    )

VAR InitiatedAmount =

    CALCULATE (

        BudgetTotal,

        'Projected WO'[WO_Status] IN { "Initiated", "initiated" }

    )

VAR OpenAmount =

    CALCULATE (

        BudgetTotal,

        'Projected WO'[WO_Status] IN { "open", "Open" }

    )

VAR AdjustedProjectedAmount = MAX( (ProjectedAmount - InitiatedAmount - OpenAmount ), 0)

RETURN

    IF (

        NOT ( WOstatus IN { "Projected", "projected" } ),

        BudgetTotal,

        AdjustedProjectedAmount)

[Anonymous]

Hi @Bunmz_a ,

Please try:

BudgetCorrectTotal =
VAR IsProjected =
    HASONEVALUE ( 'Projected WO'[WO_Status] )
        && VALUES ( 'Projected WO'[WO_Status] ) IN { "Projected", "projected" }
VAR AdjustedProjectedAmount =
    IF (
        IsProjected,
        MAX ( ( [ProjectedAmount] - [InitiatedAmount] - [OpenAmount] ), 0 ),
        [BudgetTotal]
    )
RETURN
    IF (
        ISINSCOPE ( 'Projected WO'[WO_Status] ),
        AdjustedProjectedAmount,
        SUMX (
            VALUES ( 'Projected WO'[WO_Status] ),
            [AdjustedProjectedAmount]
        )
    )

I would be grateful if you could provide me with sample data with the personal information erased.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Bunmz_a]

Unfortunately, it didnt work.

The screenshot below shows what the issue is. Unfortunately I cant attched the PBIX as I dont see the option to here

[Anonymous]

Hi @Bunmz_a ,

You mean "Revised Budget" should be "$20,935,403" instead of "$20,946,636". I hope I understand correctly.

Please try:

Revised Budget = 
VAR WOstatus =
    FIRSTNONBLANK ( 'Projected WO'[WO Status], 1 )

VAR BudgetTotal =
    CALCULATE (
        SUM ( 'Workorder Data'[Budget (With Projected Adjustment)]))

    
VAR ProjectedAmount =
    CALCULATE (
        BudgetTotal,
        'Projected WO'[WO Status] IN { "Projected", "projected" }
    )

VAR InitiatedAmount =
    CALCULATE (
        BudgetTotal,
        'Projected WO'[WO Status] IN { "Initiated", "initiated" }
    )

VAR OpenAmount =
    CALCULATE (
        BudgetTotal,
        'Projected WO'[WO Status] IN { "open", "Open" }
    )

VAR AdjustedProjectedAmount = MAX( (ProjectedAmount - InitiatedAmount - OpenAmount ), 0)

RETURN
    IF (
        NOT ( WOstatus IN { "Projected", "projected" } ),
        BudgetTotal,
        AdjustedProjectedAmount
    )

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Bunmz_a]

Hi Sorry for the late response. 

this is not the solution as the Budget Total uses the column that already has the answers (which was just for illustrative purposes). 

in the original problem, Budget with Adjusted Projected Amount does not exist. 

[Sahir_Maharaj]

Hello @Bunmz_a,

Can you please try the following DAX:

Adjusted Budget = 
VAR BudgetTotal = 
    CALCULATE (
        SUM ( Budget[Amount] ),
        Budget[BudgetType] = "Budget"
    )
VAR ProjectedAmount = 
    CALCULATE (
        BudgetTotal,
        'Projected WO'[WO_Status] IN { "Projected", "projected" }
    )
VAR InitiatedAmount = 
    CALCULATE (
        BudgetTotal,
        'Projected WO'[WO_Status] IN { "Initiated", "initiated" }
    )
VAR OpenAmount = 
    CALCULATE (
        BudgetTotal,
        'Projected WO'[WO_Status] IN { "Open", "open" }
    )
VAR AdjustedProjectedAmount = 
    MAX( ProjectedAmount - InitiatedAmount - OpenAmount, 0 )
VAR IsTotal = 
    ISINSCOPE('Projected WO'[WO_Status])

RETURN
    IF (
        IsTotal,
        SUMX(
            VALUES('Projected WO'[WO_Status]),
            IF (
                'Projected WO'[WO_Status] IN { "Projected", "projected" },
                AdjustedProjectedAmount,
                BudgetTotal
            )
        ),
        IF (
            'Projected WO'[WO_Status] IN { "Projected", "projected" },
            AdjustedProjectedAmount,
            BudgetTotal
        )
    )

Hope this helps.

[Bunmz_a]

Hi Sahir, it didn't work. I ended up with the same grand total which still reflects the original projected budget