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 table) in a month. Month is selected by a filter which is orderdate(in invoice table).

How can I show that in a table?

It is showing me duplicate values for the same customer.

e.g

If customer A has ordered 10 on 1st

and 20 on 15th

it shows me 2 rows in the table.

----------------------------------------

I have used TopN by itemQty in CustomerID 

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.

     

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi mnarmeen

     

    Given that I understand your data model and what you want to achieve, there is several ways to go about this.

     

    Personally, I like to use LOOKUPVALUE() to gather all the data, which I want to slice on, in one table, when I have tables, which aren't all related to eachother. I'll elaborate on this later on.

     

    Could you provide a screenshot of what you have now, and maybe tell a little more about how the data is stored?

     

    Let me know, if you want me to elaborate on anything.

     

    Best,

    Martin

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        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