How to create a measure to find the last value of group of categories

[Anonymous]

Categories	Quantity	Sort
a	54	1
a	76	2
a	8	3
a	7	4
a	4	5
b	2	1
b	6	2
b	68	3
b	454	4
b	97	5
b	2	6
c	9	1
c	5	2
c	4	3
c	7	4
c	54	5

I have a data set that look like above and I want to create a measure calculation to find the last value of Quantity column by grouping with Categories column.

I c ould create a new table using the following dax measure, but I want to create a measure not a table. 

Table New = SUMMARIZE(
	Table,Table[Categories],
	"Last Value",
	CALCULATE(SUM(Table[Quantity]),
		FILTER(Table,Table[Sort]=Max(Table[Sort]))
	)
	)

Could anyone please help me to use this logic and create a measure rather than calculated table. 

[FrankAT · Accepted solution]

Hi @Anonymous 

you can do it like this:

Last Value =
CALCULATE (
    MAX ( 'Table'[Quantity] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Categories] ),
        'Table'[Sort] = MAX ( 'Table'[Sort] )
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[aj1973]

Hi @Anonymous or try this

Last value =

var _Category = SELECTEDVALUE(Sheet2[Categories])

var _LastSort = MAX(Sheet2[Sort])

return

CALCULATE(SUM(Sheet2[Quantity]) , Sheet2[Categories] = _Category , Sheet2[Sort] = _LastSort)

[Jihwan_Kim]

https://www.dropbox.com/s/859um2z34didt66/surafel.pbix?dl=0 

[parry2k]

@Anonymous try this measure

Top Qty = CALCULATE ( SUM ( Cat[Quantity] ), TOPN ( 1,  ALLSELECTED (  Cat[Sort] ), CALCULATE ( MAX ( Cat[Sort] ) ), DESC ) ) 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡