Total Value not Summing Row Values?

[shanestocks]

Hi all. I have created an "aged stock analysis". One of the columns is "Production Quantity", which simply looks at the earliest date a batch existed, and tries to SUM the total quantity in KG (another column) of that.

The calculated column, called "Earliest Batch Date", to retrieve the first date a batch number was found is:

CALCULATE(MIN(uk_sap_inventory[Date created]),

ALLEXCEPT(uk_sap_inventory, uk_sap_inventory[Batch], uk_sap_inventory[Visual Material]))

I then use that calculated column into a measure called "Production Date" like so:

MIN(uk_sap_inventory[Earliest Batch])

Finally, I then create a measure called "Production Quantity" using the following:

VAR productionDate = [Earliest Batch Date]

RETURN

CALCULATE(

[Quantity (KG)],

uk_sap_inventory[Date created] = productionDate)

This works great at the row level, but the total level is wrong. Please see snippit below:

I know why it is happening but can't figure out how to rectify it. The total row (material) is taking the lowest batch date and summing that up, leaving out any other dates (in this case 21/08/24). It is therefore showing a value of 30,121, whilst the actual value here is 87,577.

Please help! How can I sort this? The row data is absolutely spot on, but the total for that 1 column is completely wrong 😞

This is the raw data showing the same batch number as an example:

Visual Material Date created Profit Ctr Plnt SLoc Batch Quantity (UOM) Value Stock Age Earliest Batch

10078 - REDACTED	21/08/2024	REDACTED	D551	LN01	20250820	5904.00	82969.51	0	21/08/2024
10078 - REDACTED	21/08/2024	REDACTED	P501	SQ01	20250820	63.00	885.35	0	21/08/2024
10078 - REDACTED	22/08/2024	REDACTED	D551	LN01	20250820	6786.00	95364.34	1	21/08/2024
10078 - REDACTED	22/08/2024	REDACTED	P501	SQ01	20250820	63.00	885.35	1	21/08/2024

Many thanks.

[Anonymous · Accepted solution]

Hi lbendlin ,thanks for the quick reply, I'll add more.

Hi @shanestocks ,

It is recommended that you use the 'ISINSCOPE' function with the IF function

ISINSCOPE function (DAX) - DAX | Microsoft Learn

I assumed some simple data

Regarding your question, the row totals are calculated based on how they are calculated in your measure.Suppose I need to get the maximum value, but in the row total I need to calculate the sum of the maximum values, this will happen.

You need to store the maximum value in a virtual table you created, and then use the 'ISINSCOPE' function to perform calculations.

Measure 2 = 
VAR _table = SUMMARIZE('Table','Table'[Type],'Table'[group],"Value",[Measure])    //Create a virtual table to store values
VAR _isParent = ISINSCOPE('Table'[group])
VAR _group = SELECTEDVALUE('Table'[group])
RETURN 
IF(_isParent = FALSE(),
SUMX(_table,[Value]),
SUMX(FILTER(_table ,[group] = _group),[Value])
)

Best Regards,
Wenbin Zhou

[lbendlin]

Remember that the total is computed differently than the row level  (in a matrix visual you actually have FOUR different calculations).

Your sample data is insufficent.  Provide at least two Visual Materials.  Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.