SUM & SUMX causing different results, how do I resolve

[jpdamd90]

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),

    0

)

TotalFORECASTEDTOCOMPLETIONWEIGHT =

SUMX(

    DISTINCT_LEVEL_ELEMENT_TABLE,

    IF(

        [SumBudgetSUPPLYWEIGHT] <> 0,

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

        0

    )

)

FORECASTED_AT_COMPLETION =

SUMX(

    DISTINCT_LEVEL_ELEMENT_TABLE,

    [SumInvoiceWEIGHT] +

    [SumScheduledFORECAST] +

    IF(

        [SumBudgetSUPPLYWEIGHT] <> 0,

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

        0

    )

)

[BeaBF · Accepted solution]

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

REMAINING SUPPLY_BBF = IF (

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

    0,

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

it returns the correct total.

BBF

[jpdamd90]

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 😕

[BeaBF]

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

BBF

[jpdamd90]

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),

    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]

        )

))

[BeaBF]

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

BBF

[jpdamd90]

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 

[BeaBF · Accepted solution]

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

REMAINING SUPPLY_BBF = IF (

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

    0,

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

it returns the correct total.

BBF

[jpdamd90]

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!

[BeaBF]

@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

[jpdamd90]

Brilliant bud thanks so much

[BeaBF]

@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]),

           0)

    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

[jpdamd90]

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? 

[BeaBF]

@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]),

           0)

    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

[jpdamd90]

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 😞

[BeaBF]

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

BBF

[jpdamd90]

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

[BeaBF]

@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

[jpdamd90]

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

[BeaBF · Accepted solution]

@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([SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost] > ([SumBudgetsupplycost] + [SumbudgetInstallCost]), [SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost] + [REMAINING SUPPLY_BBF] * 1800 + [REMAINING INSTALL_BBF] * 1200))

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

BBF

[jpdamd90]

Think I'm getting closer to gettin the totals resolved. I've used the below measures which I found from the youtube video linked. I now have the SubTotal for each measure showing correctly but can figure out away to get the Grand Total to calculate correctly. Any ideas? 
https://www.youtube.com/watch?v=dgwsk2fjWLc

TESTING FAC = IF(

    ISINSCOPE(DISTINCT_LEVEL_ELEMENT_TABLE[Element]),

    [TESTING FORECASTED AT COMPLETION],

    SUMX(SUMMARIZE(DISTINCT_LEVEL_ELEMENT_TABLE, DISTINCT_LEVEL_ELEMENT_TABLE[Element],"_Value",[TESTING FORECASTED AT COMPLETION]),[_Value])

)

TESTING LEVEL FAC =

IF(

    ISINSCOPE(DISTINCT_LEVEL_ELEMENT_TABLE[Level]),

    [TESTING FORECASTED AT COMPLETION],

    SUMX(

        SUMMARIZE(

            DISTINCT_LEVEL_ELEMENT_TABLE,

            DISTINCT_LEVEL_ELEMENT_TABLE[Level],

            "_Value", [TESTING FORECASTED AT COMPLETION]

        ),

        [_Value]

    )

)

[jpdamd90]

Finally got it resolved using the below

NEWTOTAL AT COMPLETION =

IF( ISINSCOPE( DISTINCT_LEVEL_ELEMENT_TABLE[Level] ) ,

    [TESTING FAC 2] ,

    SUMX(

        ADDCOLUMNS(

            SUMMARIZE(

                DISTINCT_LEVEL_ELEMENT_TABLE ,

                DISTINCT_LEVEL_ELEMENT_TABLE[Level] ,

                DISTINCT_LEVEL_ELEMENT_TABLE[Element] ) ,

            "@Totals" ,

            [TESTING FAC 2] ) ,

        [@Totals] ) )