Forum Discussion
jpdamd90
Helper I
Helper ISUM & 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....
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.BBFjpdamd90 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
jpdamd90Helper I
Helper IThe 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 😕