Forum Discussion
Helper ISUM & SUMX causing different results, how do I resolve
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
Super Userjpdamd90 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
Helper IApologies, 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
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
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] ) ) - jpdamd90
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
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=dgwsk2fjWLcTESTING 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]))