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

 

 

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