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.

Reply
BachDinh
Helper I
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
tamerj1
Super User
Super User

@BachDinh 

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]
)

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

@BachDinh 

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]
)

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

@BachDinh 

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.

smpa01
Super User
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

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: 

ProductGermanyUKIsrael
A1256 240
B 700600
C800800 
D643543450

 

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 DateKiosk IDProduct IDSales Amount
12/02/231A23.4
5/02/232A54.0
12/02/233B12.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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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