DAX nth largest value

[dicken]

Hi,
Is there a way of getting the nth largest value using DAX, something like LARGE in excel or list.firstN ( list, count ) {x} ?

Richard

 

[GraH]

Maybe you are looking for TOPN?

 

[dicken]

Maybe you are looking for TOPN?

Well I wasn't aware of TOPN, so thanks for that but no,
if I put

mmax:=MAX(Table1[Sales])

as a measure I get the max value, but I want the second highest value,
not the top two and not a table which is was TOPN returns



EVALUATE
TOPN ( 2, Table1, Table1[Sales] )

Gigi 3933 RRP
Tom 4831 ABC

Richard

 

[GraH]

Hi, I meant like this.

Book1
	B	C	D	E	F	G	H	I	J	K	L
1
2
3		Sales Rep	Amount	Product		Row Labels	2ndLargest
4		Gigi	3933	RRP		ABC	$4.831
5		Tom	4831	ABC		RRP	$2.674
6		Dirk	5873	ABC		Grand Total	$2.393
7		Paula	2393	ABC
8		Jody	1564	RRP		=Maxx(TOPN(2;Table1;[Amount];ASC);[Amount])
9		Miriam	2674	RRP
10		Ian	4021	RRP
11
Sheet1

 

[dicken]

Sorry , could you elaborate?

 

[GraH]

Sorry, max should be min obviously. I must have been distracked this morning.
If TOPN returns a table, one can get min from that table.

 

[dicken]

Sorry, max should be min obviously. I must have been distracked this morning.
If TOPN returns a table, one can get min from that table.

You've used semi colon , I've never used them except in excel ,( rows, column) , I can see what it's supposed to do but have yet to get a value returned ? I've tried MIN( TOPN(2, Table1), Table1[Value]) , but this doesn't work.

RD

 

[GraH]

Yes, I'm on EU settings, so my ";" is your ",". I usely do think to update it, but not always.
Did you try with MINX?

 

[dicken]

Yes, I'm on EU settings, so my ";" is your ",". I usely do think to update it, but not always.
Did you try with MINX?

Oh, thanks, I didn't know that, so thanks for letting me know that, so in excel
in a formula like

  COUNTIFS(B2:B11,{"hoodie","Shirt"},C2:C11,{"S";"L"})

would the ; and , be the other way round?
I've not got the nth smallest , largest working yet, if i do I'll let you know.

Richard.

 

[RoryA]

And cross-posted (again):

Using DAX to return the Nth highest or lowest value

Hello I have posted this on Mr Excel, but as yet have not had a successful answer; I want to get the Nth largest of smallest value using DAX , so in EXCEL you'd use LARGE or SMALL. https://www.mrexcel.com/board/threads/dax-nth-largest-value.1208250/...

www.excelforum.com