locked
Nth top value help RRS feed

  • Question

  • I wrote this formula to get the Nth top value. What is wrong in my syntax? Maybe a parentheses somewhere? 

    Nth best:=CALCULATE([total sales],topn(1,Topn([topn],all(Table1),calculate([total sales],ALLEXCEPT(Table1,Table1[Customer])),calculate([total sales],ALLEXCEPT(Table1,Table1[Customer])),1)))

    Friday, December 23, 2016 10:54 PM

Answers

  • Hello, 

    Please let me know if it works. 

    Nick - 


    CALCULATE (
        [total sales],
        TOPN (
         1,
         TOPN (
              [topn],
              ALL ( Table1 ),
              CALCULATE ([total sales],ALLEXCEPT ( Table1, Table1[Customer] ),          CALCULATE ( [total sales], ALLEXCEPT ( Table1, Table1[Customer] ) )
                ), 1
            )
        )
    )


    • Edited by Nick Chobotar Saturday, December 24, 2016 3:43 AM
    • Marked as answer by AlexMartini Monday, December 26, 2016 6:59 AM
    Saturday, December 24, 2016 3:39 AM

All replies

  • Hello, 

    Please let me know if it works. 

    Nick - 


    CALCULATE (
        [total sales],
        TOPN (
         1,
         TOPN (
              [topn],
              ALL ( Table1 ),
              CALCULATE ([total sales],ALLEXCEPT ( Table1, Table1[Customer] ),          CALCULATE ( [total sales], ALLEXCEPT ( Table1, Table1[Customer] ) )
                ), 1
            )
        )
    )


    • Edited by Nick Chobotar Saturday, December 24, 2016 3:43 AM
    • Marked as answer by AlexMartini Monday, December 26, 2016 6:59 AM
    Saturday, December 24, 2016 3:39 AM
  • Hi AlexMartini,

    Could you please share the sample data and what you want for further analysis? I am not able to reproduce your scenario based on your description.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 26, 2016 6:32 AM