Forum Discussion

mnarmeen's avatar
mnarmeen
Advocate I
9 years ago
Solved

TOP N

Hello Experts,    I need some suggestions. I have three tables 'invoice' , 'enteries' , 'customers' I want to shop TOP 5 customers(in customers table) who ordered the most quantity(in enteries tab...
Need Help
Show and Tell
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi mnarmeen,

    >>The id e,g 878 is repeating again and again

    TOPN function returns the top N rows of the specified table. When you use [itemQty] to filter the report, it returns all the top 5 rows based on [itemQty], it did't care the customer ID if is same.

    For your requirement, please create a measure get the sum of each customers.

    sum=CALCULATE(SUM(entries[itemQty]),ALLEXCEPT(Custom, Custom[ID]))


    Then create another measure used to filter the top 5 customers.

    RANK=RANKX(ALLSELECTED(Custom),[sum],,DESC,Dense)


    Finally, create a table visual, select the [ID], measure [sum] as value levels. Add the measure [RANK] as Visual level Filters, let the RANK value is less than or equals to 5, please review the following screenshot.



    Best Regards,
    Angelia

  • mnarmeen's avatar
    mnarmeen
    9 years ago

    Thankyou so much for the help. However I got it resolved by making a measure

     

    sumItemQty=sum(enteries[itemQty])

     

    and then used it in CustomerID filtering topN. 

     

    Yet, I dont know why it didnt work by simply puting ItemQty column in CustomerID topN and using SUM in aggregation.

     

mnarmeen's avatar
mnarmeen
Advocate I
9 years ago

The id e,g 878 is repeating again and again, however it must show only 19 .

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi mnarmeen,

Have you resolved your issue? Please mark the helpful or right reply as answer, so more people will benefit from here.

Best Regards,
Angelia

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    mnarmeen

     

    Did you solve your problem?

    • mnarmeen's avatar
      mnarmeen
      Advocate I
      9 years ago

      Thankyou so much for the help. However I got it resolved by making a measure

       

      sumItemQty=sum(enteries[itemQty])

       

      and then used it in CustomerID filtering topN. 

       

      Yet, I dont know why it didnt work by simply puting ItemQty column in CustomerID topN and using SUM in aggregation.

       

      • RobinSarac's avatar
        RobinSarac
        Frequent Visitor
        7 years ago

        mnarmeen I think you might have run into the issue where using a measure triggers an implicit CALCULATE and a subsequent context transition.  I'm not going to pretend I fully understand it, but I just learned about it and your comments made me think this might be helpful in understanding what's ging on and why usnig a measure helped.  This is a good place to start:

         

        https://www.sqlbi.com/articles/understanding-context-transition/

         

        I also liked the detail in this video:

         

        https://youtu.be/XWBEHOKtgP4