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 Hi! can you paste sample data on which you calculate the above formulas?

 

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