Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mnarmeen
Helper I
Helper I

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 

2 ACCEPTED SOLUTIONS

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.

2.PNG

Best Regards,
Angelia

View solution in original post

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.

 

View solution in original post

10 REPLIES 10
theov
Advocate II
Advocate II

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
Not applicable

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

 

3.PNG2.PNG1.PNG

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.

2.PNG

Best Regards,
Angelia

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

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
Not applicable

@mnarmeen

 

Did you solve your problem?

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 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
Not applicable

In what table is the "ItemQty" column?

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.