Forum Discussion

jpdamd90's avatar
jpdamd90
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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
Icon for Helper I rankHelper 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]
    )
)