Implementing a TopN filter per category

[BachDinh]

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? 

[tamerj1 · Accepted solution]

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

[BachDinh]

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

[tamerj1]

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

@BachDinh

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

[BachDinh]

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