Forum Discussion

jpdamd90's avatar
jpdamd90
Helper I
2 years ago
Solved

SUM & SUMX causing different results, how do I resolve

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....
DAX
sum
sumx
  • BeaBF's avatar
    BeaBF
    2 years ago

    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
  • BeaBF's avatar
    BeaBF
    2 years ago

    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
BeaBF's avatar
BeaBF
Super User
2 years ago

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's avatar
jpdamd90
Helper I
2 years ago

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's avatar
    BeaBF
    Super User
    2 years ago

    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's avatar
    jpdamd90
    Helper I
    2 years ago

    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] ) )
  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    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's avatar
    jpdamd90
    Helper I
    2 years ago

    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's avatar
    BeaBF
    Super User
    2 years ago

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

     

    BBF

  • jpdamd90's avatar
    jpdamd90
    Helper I
    2 years ago

    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's avatar
    BeaBF
    Super User
    2 years ago

    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's avatar
    jpdamd90
    Helper I
    2 years ago

    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

  • jpdamd90's avatar
    jpdamd90
    Helper I
    2 years ago

    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]
        )
    )
  • jpdamd90's avatar
    jpdamd90
    Helper I
    2 years ago

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