Forum Discussion

BachDinh's avatar
BachDinh
Helper I
3 years ago
Solved

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 i...
Filters
Slicer
TopN
  • tamerj1's avatar
    tamerj1
    3 years ago

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

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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's avatar
BachDinh
Helper I
3 years ago

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's avatar
    tamerj1
    Community Champion
    3 years ago

    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.