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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Implementing a TopN filter per category

Hi,

I am looking to filter a visual to only include top20 most sold items in each country, but using a either a top N filter based on a sum of net sales to filter products, or a RANKX function in combination with a Top N filter, results in the visual keeping the data of the top 20 products sold globally, which is not what I want - as an example, in a matrix format, if I have a column per country and products in rows, I would like each column to have values (say, total sales) only for the rows where the product is one of the top 20 products within that country, other rows would be null.

Could someone point me in the right direction to achieve this?

1 ACCEPTED SOLUTION
Super User

Please try

Sales Top 20 Products =
IF (
NOT ISEMPTY (
INTERSECT (
VALUES ( 'Product'[Product Name] ),
TOPN (
20,
CALCULATETABLE ( VALUES ( 'Product'[Product Name] ), ALL ( 'Product' ) ),
[Total Sales]
)
)
),
[Total Sales]
)

5 REPLIES 5
Super User

Please try

Sales Top 20 Products =
IF (
NOT ISEMPTY (
INTERSECT (
VALUES ( 'Product'[Product Name] ),
TOPN (
20,
CALCULATETABLE ( VALUES ( 'Product'[Product Name] ), ALL ( 'Product' ) ),
[Total Sales]
)
)
),
[Total Sales]
)

Helper I

Hi tamerj!

This seems to be working, massive thanks!

If it's not too much trouble, could you explain the logic of it?

I get that it's a intersect of the top 20 product sales table and the all products table, but not sure what's the NOT ISEMPTY do?

Thank you,

B

Super User
``````Sales Top 20 Products =
IF (
NOT ISEMPTY (
INTERSECT (
VALUES ( 'Product'[Product Name] ),
TOPN (
20,
CALCULATETABLE ( VALUES ( 'Product'[Product Name] ), ALL ( 'Product' ) ),
[Total Sales]
)
)
),
[Total Sales]
)``````

The intersection is between the products that are avialable in the current filter context and the top 20 products. If the the result of this intersection is NOT an empty table (the current product is one of the top 20 products) then the measure is calculated otherwise a blank will be returned.

Super User

@BachDinh

Could someone point me in the right direction to achieve this? - sure, with sample data and expected output clearly provided

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Helper I

Hi smpa,

The expected output (for ease this is just top 3) would be something like this: Total Sales by Country for top 3 products in each country:

 Product Germany UK Israel A 1256 240 B 700 600 C 800 800 D 643 543 450

So as you see, each country might have a different set of top 3 products but I would like to include all the top 3 products of all countries while excluding the total sales values where it doesn't belong to a top 3 for a specific column/country.

The most important sample data is as follows (store ID is linked to a stores table which contains the Country information, and date is linked to a calendar table):

 Sales Date Kiosk ID Product ID Sales Amount 12/02/23 1 A 23.4 5/02/23 2 A 54.0 12/02/23 3 B 12.0

I already have measures in place for totaling the sales, I am just struggling with inclusion of top N products per category

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors