Total By Last Records in a group

[lkhall]

Good morning, 

I have a table that for each "Product Name" with a "Product Category" = 'Neuromodulator' I need to identify the MAX(fact inventory pk) and return the associated "Cumulative Stock" grouped by "Center Name". I have a measure that will return this correctly for each individual product in a center but now I want to just return the sum of all the records returned by center. 

so in the case of the above image I now just want to return 1 record of 600. Below I have provided the measure that I am using to get the last record and its assoicated stock levels by Product Name. Any input would be greatly appreciated. 

Current On Hand = 

var _id = CALCULATE(
            MAX(FactInventory[fact inventory pk]),
            REMOVEFILTERS(FactInventory[Is Current Audit])
        )

return 

CALCULATE(
    SUM(FactInventory[Cumulative Stock]),
    FactInventory[fact inventory pk] = _id,
    REMOVEFILTERS(FactInventory[Is Current Audit])
)

[v-zhengdxu-msft · Accepted solution]

Hi @lkhall 

Maybe you can use summarize() function to create a virtual tables in new measure and use the sumx() to get the sum.

Here for your reference:

Then add a new measure:

Measure 2 = 
	VAR _virtualtable = SUMMARIZE(
		ALLSELECTED('Table'),
		'Table'[Name],
		"Values", [Measure]
	)
	RETURN
		SUMX(
			_virtualtable,
			[Measure]
		)

The result is as follow:

Best Regards

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