TOP N

[mnarmeen]

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 

[v-huizhn-msft · Accepted solution]

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

[theov]

Hi there, if you want to make a dynamic Top N in slicer check this out 🙂
https://www.youtube.com/watch?v=A2K-leEcgY8

[Anonymous]

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

[mnarmeen]

[v-huizhn-msft · Accepted solution]

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]

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

[v-huizhn-msft]

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]

@mnarmeen

Did you solve your problem?

[mnarmeen · Accepted solution]

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]

@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

[Anonymous]

In what table is the "ItemQty" column?

It's important that you use columns from the same table when making the TOPN.