topic Re: Problem with Grand Total of Measure in HASONEVALUE in DAX Commands and Tips

Problem with Grand Total of Measure in HASONEVALUE

Bunmz_a — Fri, 01 Mar 2024 12:22:12 GMT

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)

Re: Problem with Grand Total of Measure in HASONEVALUE

Sahir_Maharaj — Fri, 01 Mar 2024 21:05:04 GMT

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.

Re: Problem with Grand Total of Measure in HASONEVALUE

Bunmz_a — Fri, 01 Mar 2024 22:08:03 GMT

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

Re: Problem with Grand Total of Measure in HASONEVALUE

Anonymous — Mon, 04 Mar 2024 06:24:32 GMT

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!

Re: Problem with Grand Total of Measure in HASONEVALUE

Bunmz_a — Mon, 04 Mar 2024 16:22:48 GMT

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

Re: Problem with Grand Total of Measure in HASONEVALUE

Anonymous — Fri, 08 Mar 2024 05:24:47 GMT

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

Re: Problem with Grand Total of Measure in HASONEVALUE

Bunmz_a — Tue, 26 Mar 2024 15:30:51 GMT

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.