Multiple Top N

[buttercream]

Hi,

I have 2 tables.

Table1

Category	Product
Fruits	Apple
Fruits	Banana
Fruits	Kiwi
Fruits	Mango
Vegetables	Celery
Vegetables	Lettuce
Vegetables	Carrot
Vegetables	Onion
Grains	Rice
Grains	Bread
Grains	Pasta
Grains	Beans
Meats	Chicken
Meats	Beef
Meats	Turkey

 

Table 2

Date	Product	Sale
2/5/2024	Banana	4
2/5/2024	Lettuce	19
2/5/2024	Onion	14
2/5/2024	Beans	5
4/10/2024	Lettuce	13
4/10/2024	Carrot	12
4/10/2024	Onion	18
4/10/2024	Mango	14
4/10/2024	Chicken	20
4/10/2024	Beef	20
4/10/2024	Beans	15
5/1/2024	Beans	19
5/1/2024	Rice	18
5/1/2024	Onion	12
5/1/2024	Lettuce	7
5/1/2024	Celery	17
5/1/2024	Carrot	18
5/1/2024	Banana	10

I am trying to get a visual that shows top 2 categories for the latest date selected via a date slicer.  Example if no date is selected in slicer would default to 5/1/2024 (latest date).

Category	Sale
Vegetables	54
Grains	37

How do I achieve this?

[collinq · Accepted solution]

Hy @buttercream ,

Assuming that you have a DateDimension that is linked to the sales date and assuming that you are linking the two examples based on the Product field; then the Top N will get you want you want.  By doing this version you get it by date:

If you want to ignore the  date, change the top N by Sale instead of Date

If you want to have this done by a specific date, you could use either method and then make a Dax date of "latest date" and then put that into the filter for the visual.