Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

TopN function cant display on the matrix

Hi, community, 

Please see the attached picture. I have a TOPN measure. I would like to get the top 10 customers based on the sales amount.

TOP10PCUSTOMERBYSALES1 = TOPN(10,VALUES(Orders[Customer ID]),SUM(Orders[Sales]),DESC)
When showing the measure in a table matrix, it gives the error below.
Above measure as illustrated in the book named "Exam Ref 70-778 analyzing and visualizing data by using Microsoft Power BI. It does not work in my case. 
 
If anyone can explain the logic and reason why it is wrong, also provide a solution that will be great. thank you!

 

Need Help
Tips and Tricks

4 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    Anonymous  It looks like you're trying to use a Table function inside a MEASURE, so TOPN is returning an entire table of values, but you need to get that down to 1 scalar expression, which is what amitchandak 's suggestion will do for you. 

     

    By putting the TOPN inside a filter expression of a CALCULATE, you are forcing the measure to only calculate for the TOPN values, but you still need to define what the measure actually is (ie SUM(Orders[Sales]). If you do this only, then you will get the same value for every customer, ie total sales for top 10 customers combined: 

     

    TotalSalesforTop10Customers =
    CALCULATE(SUM(Orders[Sales]), TOPN(10,VALUES(Orders[Customer ID]), SUM(Orders[Sales]), DESC))

     

    IF you use a variable, then you can return each customer's total sales for only the Top10 customers:

    Top10CustomersbySales =
    VAR ranking= VALUES(Orders[Customer ID])
    RETURN
    CALCULATE(SUM(Orders[Sales]), TOPN(10,ALL(Orders[Customer ID]), SUM(Orders[Sales]), DESC),ranking)

     

    Otherwise do your formula as a new TABLE rather than a MEASURE. 

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv

    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      6 years ago

      You could also use the built in TOP N visual level filter on Customer ID (change the filter on Customer ID from Advanced or Basic to Top N) and drag Sales into the 'by value' field. 

       

      Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

       

      If you found this post helpful, please give Kudos.

      I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

      https://sites.google.com/site/allisonkennedycv

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      AllisonKennedy  thank you for the detailed explanation! that is very helpful for me to understand why... thanks again!

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , refer , if this can help

    https://databear.com/power-bi-dax-topn-function/

     

    TOP10PCUSTOMERBYSALES1 =
    VAR
    ranking = VALUES(Orders[Customer ID])
    Return
    CALCULATE(SUM(Orders[Sales]),
    TOPN(10,ALL(Orders[Customer ID]),SUM(Orders[Sales])),
    ranking,DESC)

     

    For Rank Refer these links
    https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
    https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
    https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale