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. When I use the sum function within my 'FORECASTEDTOCOMPLETIONWEIGHT' measure i get the correct value (0 in the example below), but when i use SUMX in 'TotalFORECASTEDTOCOMPLETIONWEIGHT' it returns 15.40. I've tried debugging with chatgpt but no joy. Please help. My Forecasted_at_completion in the below should show 49.21

 





FORECASTEDTOCOMPLETIONWEIGHT =
IF(
    [SumBudgetSUPPLYWEIGHT] <> 0,
    MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0),
    0
)



TotalFORECASTEDTOCOMPLETIONWEIGHT =
SUMX(
    DISTINCT_LEVEL_ELEMENT_TABLE,
    IF(
        [SumBudgetSUPPLYWEIGHT] <> 0,
        MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0),
        0
    )
)

FORECASTED_AT_COMPLETION =
SUMX(
    DISTINCT_LEVEL_ELEMENT_TABLE,
    [SumInvoiceWEIGHT] +
    [SumScheduledFORECAST] +
    IF(
        [SumBudgetSUPPLYWEIGHT] <> 0,
        MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0),
        0
    )
)
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

21 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    jpdamd90 Hi! can you paste sample data on which you calculate the above formulas?

     

    BBF

    • jpdamd90's avatar
      jpdamd90
      Helper 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
        Super 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
    Helper I
    2 years ago

    The 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 😕