Forum Discussion

slokam's avatar
slokam
Frequent Visitor
2 years ago

Measure Total Incorrect

Hello Experts

I'm encountering a classic issue with the Measure total. I have a following Measure that works correctly at each row level in the Table visual.

 
===========================================================================
PO Allocated Quantity Test =
VAR vTotalOnHandQty = 
CALCULATE(
    SUM(ItemTransactions[Trans Quantity]),
  FILTER(     
ALL('ItemTransactions'),
    'ItemTransactions'[Item ID] = MAX('ItemTransactions'[Item ID])
    && 'ItemTransactions'[Sub Inventory] = MAX(ItemTransactions[Sub Inventory])
    && 'ItemTransactions'[Trans Date] <= MAX('Dates'[FullDateAlternateKey])
    )
)
VAR vCurrItemID = MAX('ItemTransactions'[Item ID])
VAR vCurrSubInv = MAX('ItemTransactions'[Sub Inventory])
VAR vCurrQty = MAX('ItemTransactions'[Source Trans Quantity])
 
VAR vCumulativeQtyTotal =
CALCULATE(
  SUM('ItemTransactions'[Source Trans Quantity]),
  FILTER(
    ALL('ItemTransactions'),
    'ItemTransactions'[Item ID]  =  vCurrItemID &&
    'ItemTransactions'[Sub Inventory] = vCurrSubInv &&     
    'ItemTransactions'[PO Line ID] >= MIN(ItemTransactions[PO Line ID]) && 
    NOT ISBLANK(ItemTransactions[PO Header ID])
  )
)
VAR vPrvCumulativeQtyTotal = vCumulativeQtyTotal - vCurrQty
VAR vAllocatedQty = MIN(vCurrQty,vTotalOnHandQty - vPrvCumulativeQtyTotal)
RETURN IF (vAllocatedQty < 0, 0, vAllocatedQty)
============================================================================
 
To get the measure total accurately, I created another wrapper measure as follows. This works correctly at the row level in the Table visual, but the wrapper total measure still does not give the correct totals.
=========================================
PO Allocated Quantity By Trans Number Test =
IF(
ISINSCOPE('ItemTransactions'[Source Trans Number]),
[PO Allocated Quantity Test],
SUMX(
SUMMARIZE(
'ItemTransactions', [Source Trans Number], "__value",[PO Allocated Quantity Test]
),
[__value]
)
)
=================================================================
 
Sample Output :

 

As you can see, the Total in the first line is still incorrect!

Any help would be greatly appreciated.

Thank you

slokam

Need Help

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi slokam -Can you check the below measure to get both row-level accuracy and correct aggregation in the visual totals

     

    PO Allocated Quantity By Trans Number Test =
    IF (
    ISINSCOPE('ItemTransactions'[Source Trans Number]),
    [PO Allocated Quantity Test],
    SUMX (
    SUMMARIZE (
    'ItemTransactions',
    'ItemTransactions'[Item ID],
    'ItemTransactions'[Sub Inventory],
    'ItemTransactions'[Source Trans Number],
    "__value", [PO Allocated Quantity Test]
    ),
    [__value]
    )
    )

     

    Hope it helps

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • slokam's avatar
      slokam
      Frequent Visitor
      2 years ago

      Hi rajendraongole1 

      Thank you very much for your quick response; it's greatly appreciated. I tried the changes you suggested and tested them. Unfortunately, the results remain the same: the row-level calculations are accurate, but the totals still don't add up. I'm including another item's results for your reference (expecting the total in the last column to be 6178.00). I'm sure I'm missing something in the measure calculation.

      Any additional insights would be greatly appreciated and would help me move forward with my work.

       

       

      Thanks

      slokam

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi slokam ,

     

    Thanks rajendraongole1  for the quick reply. I have some other thoughts to add:

    You can try to create a new measure.

    PO Allocated Quantity By Trans Number Test = 
SUMX(VALUES('ItemTransactions'[Source Trans Number]),[PO Allocated Quantity Test])

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

    • slokam's avatar
      slokam
      Frequent Visitor
      2 years ago

      HI Anonymous 

      Thank you for your proposed solution, apparently it's working for some scenarios but not all. After analysing looks like following calculation is not correct. This calculation is to get onHandQty at Item level, where as the above output is at Transaction level. Can you please let me know how can i get ITEM level( One Item can have multiple transNumbers) Totals in this calculation?

      ======================================

      VAR vTotalOnHandQty = 
      CALCULATE(
          SUM(ItemTransactions[Trans Quantity]),
        FILTER(     
      ALL('ItemTransactions'),
          'ItemTransactions'[Item ID] = MAX('ItemTransactions'[Item ID])
          && 'ItemTransactions'[Sub Inventory] = MAX(ItemTransactions[Sub Inventory])
          && 'ItemTransactions'[Trans Date] <= MAX('Dates'[FullDateAlternateKey])
          )
      )
      =======================================
       
      Also can you please clarify what's the difference between SUMMARIZE Vs VALUES solution your proposed?
       
      Thanks a lot for your help, much appreciated.
      slokam