topic Re: SUM & SUMX causing different results, how do I resolve in DAX Commands and Tips

SUM & SUMX causing different results, how do I resolve

jpdamd90 — Thu, 18 Jul 2024 05:33:45 GMT

I have a measure for Forecasted_at_completion within my datamodel. I noticed that the figures were right in the columns of my Matrix visualisation but when I looked at the totals they were incorrect. When I use the sum function within my 'FORECASTEDTOCOMPLETIONWEIGHT' measure i get the correct value (0 in the example below), but when i use SUMX in 'TotalFORECASTEDTOCOMPLETIONWEIGHT' it returns 15.40. I've tried debugging with chatgpt but no joy. Please help. My Forecasted_at_completion in the below should show 49.21

FORECASTEDTOCOMPLETIONWEIGHT =

IF(

[SumBudgetSUPPLYWEIGHT] <> 0,

MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0),

)

TotalFORECASTEDTOCOMPLETIONWEIGHT =

SUMX(

DISTINCT_LEVEL_ELEMENT_TABLE,

IF(

[SumBudgetSUPPLYWEIGHT] <> 0,

MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0),

)

FORECASTED_AT_COMPLETION =

SUMX(

DISTINCT_LEVEL_ELEMENT_TABLE,

[SumInvoiceWEIGHT] +

[SumScheduledFORECAST] +

IF(

[SumBudgetSUPPLYWEIGHT] <> 0,

MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0),

)

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Thu, 18 Jul 2024 09:47:25 GMT

jpdamd90 Hi! can you paste sample data on which you calculate the above formulas?

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Thu, 18 Jul 2024 10:23:24 GMT

Hey, thanks for replying. When you say sample data do you mean the background tables or the details of the measures that are making up the above formulas?

The issue is happening on a Distinct table i created. Code below. No matter what I try I cant seem to get the totals for any remaining value calculations working. See additional screenshots with highlighted issues. Hilighted in yellow adds up to over 286 where the total is showing as 192.88.

SumInvoiceWEIGHT =

CALCULATE(

SUM('Invoice Table'[Total Weight On Inv]) / 1000,

TREATAS(

VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Building]), 'Invoice Table'[Building]

TREATAS(

VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Level]), 'Invoice Table'[INV-Level1]

TREATAS(

VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Element]), 'Invoice Table'[INV-Element]

)

SumScheduledFORECAST =

CALCULATE(

SUM('Sch_details'[TOTAL_WEIGHT_TONNES]), -- Replace with the correct column name

EXCEPT(

VALUES('Sch_details'[Schedule Number Format]),

VALUES('Invoice Table'[Schedule Number])

TREATAS(VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Building]), 'Sch_details'[Building]),

TREATAS(VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Level]), 'Sch_details'[Sch Level]),

TREATAS(VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Element]), 'Sch_details'[SCH Element])

)

FORECASTEDTOCOMPLETIONWEIGHT =

IF(

[SumBudgetSUPPLYWEIGHT] <> 0,

MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0),

)

DISTINCT_LEVEL_ELEMENT_TABLE =

DISTINCT(

UNION(

SELECTCOLUMNS(

'Budget',

"Building", 'Budget'[Building],

"Level", 'Budget'[Level],

"Element", 'Budget'[Element]

SELECTCOLUMNS(

'CombinedInvoiceTable',

"Building", 'CombinedInvoiceTable'[Building],

"Level", 'CombinedInvoiceTable'[Level],

"Element", 'CombinedInvoiceTable'[Element]

SELECTCOLUMNS(

'Sch_details',

"Building", 'Sch_details'[Building],

"Level", 'Sch_details'[Sch Level],

"Element", 'Sch_details'[SCH Element]

SELECTCOLUMNS(

'Claim Details',

"Building", 'Claim Details'[Building],

"Level", 'Claim Details'[LEVEL],

"Element", 'Claim Details'[ELEMENT]

)

))

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Thu, 18 Jul 2024 10:25:55 GMT

jpdamd90 the dataset. Can you share the pbix file? or paste some sample data, so that i can recreate your scenario.

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Thu, 18 Jul 2024 10:33:11 GMT

See link below to onedrive with trimmed down pbix. Apologies in advance for the state of the data model, building it adhoc with not much knowledge or time.PIBX File

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Thu, 18 Jul 2024 14:01:37 GMT

jpdamd90 here is the solution, try with this updated formula in Remaining Supply:

REMAINING SUPPLY_BBF = IF (

[SumInvoiceWEIGHT] + [SumScheduledFORECAST] > [SumBudgetSUPPLYWEIGHT],

SUMX(DISTINCT_LEVEL_ELEMENT_TABLE, [SumBudgetSUPPLYWEIGHT]) - SUMX(DISTINCT_LEVEL_ELEMENT_TABLE, [SumInvoiceWEIGHT]) + SUMX(DISTINCT_LEVEL_ELEMENT_TABLE, [SumScheduledFORECAST]))

it returns the correct total.

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Thu, 18 Jul 2024 21:53:09 GMT

Legend mate thank you so much. Is there anyway you could help me with my Forecasted at completion, it doesnt seem to be calculating correctly either.
See example below under my accessories element. B1 has a actually supply of $57073 & a budgeted install of $9104 so the forecasted at completion should be 66,177 but it shows as $57073.
Then L4 has a supply budget of $9801, an install budget of $18431 which should give a total of $28,232 but it is displaying $31,577.

And a potentially more complex issue I'm having is figuring out a way to use previous overrun from completed elements to update the forecasted at completion for uncompleted elements. EG- if JUMPFORM for levels B3, B2 & B1 is completed and has finished with an actual supply and install tonnage and cost that is on average 10% higher than Budgeted, then the forecasted at completion would use the budget for levels GF and up with a 10% additional for working out the forecasted at completion.

Really appreicate your time and help!

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Fri, 19 Jul 2024 07:03:46 GMT

jpdamd90 please accept the first answer as a solution. As soon as I can I'll try to look at the other points you wrote to me and let's do some tests!

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Fri, 19 Jul 2024 07:06:29 GMT

Brilliant bud thanks so much

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Fri, 19 Jul 2024 07:28:45 GMT

jpdamd90 So, first point, the forecasted, It appears you are not getting the correct fields for the operation you describe. In fact, taking the fields you indicated, the correct result is 66,176:

FORECASTEDCOST_INSTALL_BBF =

SUMX(

'DISTINCT_LEVEL_ELEMENT_TABLE',

VAR RemainingWeightToInstall = [FORECASTED_AT_COMPLETION] - [SumClaimWeight]

VAR RemainingCostToInstall = RemainingWeightToInstall * 1200

VAR RemainingWeightToSupply =

IF([SumBudgetSUPPLYWEIGHT] > ([SumInvoiceWEIGHT] + [SumScheduledFORECAST]),

[SumBudgetSUPPLYWEIGHT] - ([SumInvoiceWEIGHT] + [SumScheduledFORECAST]),

VAR RemainingCostToSupply = RemainingWeightToSupply * 1800

VAR TotalInvoiceAndSCHEDULEDWEIGHT = SUMX(DISTINCT_LEVEL_ELEMENT_TABLE, [SumInvoiceWEIGHT] + [SumScheduledFORECAST])

VAR TotalInvoiceAndClaimCost = SUMX(DISTINCT_LEVEL_ELEMENT_TABLE, [SumTotalInvoice] + [SumClaimCOST])

RETURN

[SumTotalInvoice] + [SumBudgetINSTALLCOST] )

Output:

If it's ok, accept the solution and i'll pass to the next topic.

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Fri, 19 Jul 2024 07:36:47 GMT

Thanks BBF, It still doesn't seem to be returning the correct value for the other levels further down the list. It looks to only return the budget Install Cost not the combined budget install and budget supply?

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Fri, 19 Jul 2024 07:49:55 GMT

jpdamd90 because i did [SumTotalInvoice] + [SumBudgetINSTALLCOST], that is SUPPLY ($) + BUDGET INSTALL ($), so try with this updated:

FORECASTEDCOST_INSTALL_BBF =

SUMX(

'DISTINCT_LEVEL_ELEMENT_TABLE',

VAR RemainingWeightToInstall = [FORECASTED_AT_COMPLETION] - [SumClaimWeight]

VAR RemainingCostToInstall = RemainingWeightToInstall * 1200

VAR RemainingWeightToSupply =

IF([SumBudgetSUPPLYWEIGHT] > ([SumInvoiceWEIGHT] + [SumScheduledFORECAST]),

[SumBudgetSUPPLYWEIGHT] - ([SumInvoiceWEIGHT] + [SumScheduledFORECAST]),

VAR RemainingCostToSupply = RemainingWeightToSupply * 1800

VAR TotalInvoiceAndSCHEDULEDWEIGHT = SUMX(DISTINCT_LEVEL_ELEMENT_TABLE, [SumInvoiceWEIGHT] + [SumScheduledFORECAST])

VAR TotalInvoiceAndClaimCost = SUMX(DISTINCT_LEVEL_ELEMENT_TABLE, [SumTotalInvoice] + [SumClaimCOST])

RETURN

IF( NOT ISBLANK([SumTotalInvoice]),

[SumTotalInvoice] + [SumBudgetINSTALLCOST], [SumBudgetSUPPLYCOST] + [SumBudgetINSTALLCOST]))

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Fri, 19 Jul 2024 08:22:21 GMT

Still some issues when I look at the other elements mate sorry. See below. Your total shows as $110,866 where the calc should be Actual Supply $ 62735 + actual install $ 70776 + sch cost $27338 + Remaining install (3.64 * 1200 = $4368) = $165,217 😞

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Fri, 19 Jul 2024 08:26:12 GMT

jpdamd90 i need you to explain the rule, when this field must do a calculation, when another, ecc...

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Fri, 19 Jul 2024 08:35:56 GMT

Sorry I didn't explain the logic behind it very well. So its trying to get a forecasted at completion cost for the level/element.

If Supply Budget and Install Budget are higher than actual supply, actual install and sch forecast then the forecasted at completion would just be the budgets combined.

If the supply/install/sch combined are already higher than the budget, the element has gone over budget and the forecasted at completion will be the Total of Supply $ + Install $ + Sch $ + Supply remaining (T) * 1800 + Install Remaining * 1200

IIf possible then for completed elements (Ones that have YES in the Complete column. Calculate the average variance from the budget and have this applied to the same element budget for later levels. That way the forecasted at completion would be

If the Budget supply + the budget install + the Average % overrun from previous levels for that element is greater than the actual supply + install + sch cost then it would display as the budget with that % added on.

Hope this makes sense. thanks again

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Fri, 19 Jul 2024 08:57:02 GMT

jpdamd90 i can't undestand you in this way, you need to explain to me by using field names. For example:

If [SumTotalInvoice] is blank, make

[SumBudgetSUPPLYCOST] + [SumBudgetINSTALLCOST], otherwise

[SumTotalInvoice] + [SumBudgetINSTALLCOST].... ok?

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Fri, 19 Jul 2024 09:07:03 GMT

Apologies, see below with fields included.

If Supply Budget [sumbudgetsupplycost] and Install Budget [sumbudgetinstallcost] are higher than actual supply [SumTotalInvoice], actual install [SumClaimCost] and sch forecast [Scheduled Cost] then the forecasted at completion would just be the budgets combined ([SumBudgetsupplycost] + [SumbudgetInstallCost]).

If the actual supply [SumTotalInvoice], actual install [SumClaimCost] and sch forecast [Scheduled Cost] combined are already higher than the budget ([SumBudgetsupplycost] + [SumbudgetInstallCost]), the element has gone over budget and the forecasted at completion will be the [SumTotalInvoice], actual install [SumClaimCost] and sch forecast [Scheduled Cost] + Supply remaining (T)[Remaining Supply] * 1800 + Install Remaining[Remaining Install] * 1200

Re: SUM & SUMX causing different results, how do I resolve

BeaBF — Fri, 19 Jul 2024 09:29:16 GMT

jpdamd90 The code for this rule is:

FORECASTEDCOST_INSTALL_BBF =

IF( [SumBudgetSUPPLYCOST] > [SumTotalInvoice] && [sumbudgetinstallcost] > [SumTotalInvoice] &&

[SumBudgetSUPPLYCOST] > [SumClaimCost] && [sumbudgetinstallcost] > [SumClaimCost] &&

[SumBudgetSUPPLYCOST] > [Scheduled Cost] && [sumbudgetinstallcost] > [Scheduled Cost], [SumBudgetsupplycost] + [SumbudgetInstallCost],

IF([SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost] > ([SumBudgetsupplycost] + [SumbudgetInstallCost]), [SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost] + [REMAINING SUPPLY_BBF] * 1800 + [REMAINING INSTALL_BBF] * 1200))

But i see that for accessories B1 the value is not correct, this row finishes in the second condition :

if you do the calculation with a calculator is correct, maybe is your formula not correct.

BBF

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Fri, 19 Jul 2024 23:10:41 GMT

Thanks BBF, this helped me massively to figure out what was going wrong. Relised that when there was an amount in [Sumbudgetinstall] but no weight in [remaining install] it wouldnt pick up the right figures for the calculation. Added it to what you gave me to make the below and its working now. The only issue with it is that the Total of the column doesnt seem to include the MISC total and $118 that was miscategorised as SLAB (NON-PT) when it shouldnt have had the - in. I've corrected the invoice so it now falls in to SLAB (NON PT) but the total is still out by it and the MISC Element & I've no clue why that would be happening when the column totals them up correctly 😕 Thanks

FORECASTEDCOST_INSTALL_BBF =

IF(

[SumBudgetSUPPLYCOST] > [SumTotalInvoice] && [sumbudgetinstallcost] > [SumTotalInvoice] &&

[SumBudgetSUPPLYCOST] > [SumClaimCost] && [sumbudgetinstallcost] > [SumClaimCost] &&

[SumBudgetSUPPLYCOST] > [Scheduled Cost] && [sumbudgetinstallcost] > [Scheduled Cost] &&

[SumBudgetsupplycost] + [SumbudgetInstallCost] > [SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost],

[SumBudgetsupplycost] + [SumbudgetInstallCost],

IF(

[SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost] > ([SumBudgetsupplycost] + [SumbudgetInstallCost]),

[SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost] + [REMAINING SUPPLY] * 1800 +

IF(

ISBLANK([REMAINING INSTALL]) || [REMAINING INSTALL] = 0,

[sumbudgetinstallcost],

[REMAINING INSTALL] * 1200

[SumBudgetsupplycost] + [SumbudgetInstallCost]

)

Re: SUM & SUMX causing different results, how do I resolve

jpdamd90 — Sun, 21 Jul 2024 00:36:32 GMT

The totals just don't seem to work correctly at all, see below for Jumpform. Total should be $1,695,659 based on the numbers within that element but it shows as $1,348,848 which is the total of SumbudgetSupplyCost & SumBudgetInsallCost 😕