+ Reply to Thread
Results 1 to 16 of 16

Using DAX to return the Nth highest or lowest value

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    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.

    HTML Code: 
      https://www.mrexcel.com/board/threads/dax-nth-largest-value.1208250/

    the suggestion I had was to use TOPN and MIN or MAX, so to return the 3rd hight value

    TOPN(3 , Table, Table[ Column Numbers ] )

    to return a table containing the top 3 values. then wrap in MIN to get the lowest , 3rd.
    I've tried variuos options none of which has worked,

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    etc.

    Does anyone have any suggestions?

    Richard.
    Last edited by Dicken; 06-21-2022 at 01:24 PM. Reason: made mistake

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

    Re: Using DAX to return the Nth highest or lowest value

    Hi,

    Perhaps attach a workbook with a small example together with your unsuccessful attempts.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    Re: Using DAX to return the Nth highest or lowest value

    I cant really give a full example as TOPN returns a table, not a value,
    I've tried attaching a screen shot from studio but can't get it to work. I've attached a workbook with minimum value and I want to return the 3rd , for arguments sake.

    MIN(
    TOPN( 3, Table1,Table1[Units]) )
    Attached Files Attached Files

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

    Re: Using DAX to return the Nth highest or lowest value

    For 3rd largest:

    With TOPN:

    PHP Code: 
    MINX(
        
    TOPN3SUMMARIZETable1Table1[Units] ), Table1[Units], DESC ),
        [
    Units]

    With RANKX:

    PHP Code: 
    VAR MyTable =
        
    SUMMARIZE(
            
    Table1,
            
    Table1[Units],
            
    "Rank"RANKXALLTable1 ), Table1[Units] )
        )
    RETURN
        
    MAXXMyTable, IF( [Rank] = 3, [Units] ) ) 
    Regards

  5. #5
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    Re: Using DAX to return the Nth highest or lowest value

    Hi,
    Haven't actually got that to work, but I'll persevere, the table returned to MIN is the Summarized table,
    but I still get an error?

    I have come up with one long winded way, involving 'helper table'
    Select a cell along table1
    DATA , Existing connections, select Table1, or whatever, past along side , right click; Table ; Edit DAX
    EVALUATE
    TOPN(3, Table1,Table1[Units])

    Enter and a table of top 3 items is returned and then a straight forward min measure ,
    I've attached example.


    RD
    Attached Files Attached Files
    Last edited by Dicken; 06-22-2022 at 05:07 AM. Reason: error

  6. #6
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    Re: Using DAX to return the Nth highest or lowest value

    Just to update , it's now working, as far as I can see the main difference is the use of SUMMARIZE and MINX rather than MIN,
    how does this make a difference?
    RD

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

    Re: Using DAX to return the Nth highest or lowest value

    MIN has to refer to an actual physical column within an existing table, so you can't pass TOPN to it (which returns a virtual table).

    Regards

  8. #8
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    Re: Using DAX to return the Nth highest or lowest value

    Thanks, that is something I'd never considered, I always thought they would be treated the same.

    RD

  9. #9
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    Re: Using DAX to return the Nth highest or lowest value

    Just to add I've put all the different stages into studio, I've only been using it a couple of weeks and don't know how i managed before,

    I'm still not sure why SUMMARIZE, can see a single column table returned, as opposed to the entire table

    I know this doesn't work but can't see why;

    Please Login or Register  to view this content.
    can't work,

    Richard.

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,219

    Re: Using DAX to return the Nth highest or lowest value

    You've got too many arguments there. It would just be:

    Please Login or Register  to view this content.
    Rory

  11. #11
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    Re: Using DAX to return the Nth highest or lowest value

    Yes, you are right , once again, when I first started trying to solve this I had too few, can't win.

    rd

  12. #12
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    Re: Using DAX to return the Nth highest or lowest value

    Really sorry, I mistakenly deducted reputation, meant to click approve, if I can reverse this I will.

    Richard

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Using DAX to return the Nth highest or lowest value

    Quote Originally Posted by Dicken View Post
    Really sorry, I mistakenly deducted reputation, meant to click approve, if I can reverse this I will.

    Richard
    I have corrected - and reversed - the neg rep you gave.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  14. #14
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

    Re: Using DAX to return the Nth highest or lowest value

    Thanks a lot, Ford!

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Using DAX to return the Nth highest or lowest value

    No problem

  16. #16
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    154

    Re: Using DAX to return the Nth highest or lowest value

    Thanks,

    rd

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Look up to return in order highest to lowest
    By TrainerJ in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-16-2017, 08:50 AM
  2. [SOLVED] Sorting from highest value to lowest and return name and not value
    By thedrinkerparadox in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-16-2017, 12:04 PM
  3. Vlookup highest to lowest and return column name
    By Stuono in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2016, 08:03 AM
  4. [SOLVED] Formula to find lowest/highest value in a column and return value to that cell
    By maryren in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2013, 11:21 AM
  5. Return Highest & Lowest Value from Vlookup
    By ioswoody in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-13-2013, 01:05 AM
  6. Replies: 7
    Last Post: 03-14-2012, 12:28 PM
  7. Return highest/lowest 6 values from a list
    By thekingsoutlaw in forum Excel General
    Replies: 4
    Last Post: 01-21-2008, 04:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1