Measure Total Incorrect

[slokam]

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

[Anonymous]

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]

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

[rajendraongole1]

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]

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