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

Hey, thanks for replying. When you say sample data do you mean the background tables or the details of the measures that are making up the above formulas?

The issue is happening on a Distinct table i created. Code below. No matter what I try I cant seem to get the totals for any remaining value calculations working. See additional screenshots with highlighted issues. Hilighted in yellow adds up to over 286 where the total is showing as 192.88.

SumInvoiceWEIGHT =
CALCULATE(
    SUM('Invoice Table'[Total Weight On Inv]) / 1000,
    TREATAS(
        VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Building]), 'Invoice Table'[Building]
    ),
    TREATAS(
        VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Level]), 'Invoice Table'[INV-Level1]
    ),
    TREATAS(
        VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Element]), 'Invoice Table'[INV-Element]
    )
)
SumScheduledFORECAST =
CALCULATE(
    SUM('Sch_details'[TOTAL_WEIGHT_TONNES]), -- Replace with the correct column name
    EXCEPT(
        VALUES('Sch_details'[Schedule Number Format]),
        VALUES('Invoice Table'[Schedule Number])
    ),
    TREATAS(VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Building]), 'Sch_details'[Building]),
    TREATAS(VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Level]), 'Sch_details'[Sch Level]),
    TREATAS(VALUES(DISTINCT_LEVEL_ELEMENT_TABLE[Element]), 'Sch_details'[SCH Element])
)
FORECASTEDTOCOMPLETIONWEIGHT =
IF(
    [SumBudgetSUPPLYWEIGHT] <> 0,
    MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0),
    0
)




 



DISTINCT_LEVEL_ELEMENT_TABLE =
DISTINCT(
    UNION(
        SELECTCOLUMNS(
            'Budget',
            "Building", 'Budget'[Building],
            "Level", 'Budget'[Level],
            "Element", 'Budget'[Element]
        ),
        SELECTCOLUMNS(
            'CombinedInvoiceTable',
            "Building", 'CombinedInvoiceTable'[Building],
            "Level", 'CombinedInvoiceTable'[Level],
            "Element", 'CombinedInvoiceTable'[Element]
        ),
        SELECTCOLUMNS(
            'Sch_details',
            "Building", 'Sch_details'[Building],
            "Level", 'Sch_details'[Sch Level],
            "Element", 'Sch_details'[SCH Element]
        ),
        SELECTCOLUMNS(
            'Claim Details',
            "Building", 'Claim Details'[Building],
            "Level", 'Claim Details'[LEVEL],
            "Element", 'Claim Details'[ELEMENT]
        )
))
BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

jpdamd90  the dataset. Can you share the pbix file? or paste some sample data, so that i can recreate your scenario.

 

BBF

  • jpdamd90's avatar
    jpdamd90
    Icon for Helper I rankHelper I
    2 years ago

    See link below to onedrive with trimmed down pbix. Apologies in advance for the state of the data model, building it adhoc with not much knowledge or time.PIBX File 

    • BeaBF's avatar
      BeaBF
      Icon for Super User rankSuper User
      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
      • jpdamd90's avatar
        jpdamd90
        Icon for Helper I rankHelper I
        2 years ago

        Legend mate thank you so much. Is there anyway you could help me with my Forecasted at completion, it doesnt seem to be calculating correctly either.
        See example below under my accessories element. B1 has a actually supply of $57073 & a budgeted install of $9104 so the forecasted at completion should be 66,177 but it shows as $57073.
        Then L4 has a supply budget of $9801, an install budget of $18431 which should give a total of $28,232 but it is displaying $31,577.

        And a potentially more complex issue I'm having is figuring out a way to use previous overrun from completed elements to update the forecasted at completion for uncompleted elements. EG- if JUMPFORM for levels B3, B2 & B1 is completed and has finished with an actual supply and install tonnage and cost that is on average 10% higher than Budgeted, then the forecasted at completion would use the budget for levels GF and up with a 10% additional for working out the forecasted at completion.

         

        Really appreicate your time and help!