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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
msmays5
Helper II
Helper II

TOP N Products for Selected Brand and TOP N Products for All Other Brands

Background

I have a model with a basic star schema (tables in bold below)

Product

Product Id

Product Name

Brand

 

Market

Market Id

Market Name

 

Sales

Market Id

Product Id

Total Sales

Total Unit Sales

 

I have the following base measures

$ Sales: SUM ( Sales[Total Sales] )

Unit Sales: SUM ( Sales[Total Unit Sales] )

 

I also have a single selection slicer with the field Product[Brand].

 

Need

I am trying to create a table that displays the Unit Sales for 10 products names:  the TOP 5 products by [$ Sales] of the selected brand AND the TOP 5 products by [$ Sales] of all other brands.

 

Plan

I was trying to create a measure that can be used to filter the products that show up. I figured I could create a measure with two virtual tables: Top5FocusBrands and Top5NonFocusBrands and then UNION them together, add a column with a 1, and then filter to non-blanks. But I can't quite get this to work. Any ideas on how to approach this problem would be greatly appreciated

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

 

Jihwan_Kim_2-1677295434974.png

 

 

Jihwan_Kim_1-1677295425808.png

 

Top five focus brand and top five nonfocus brand: =
VAR _topfivefocus =
    TOPN (
        5,
        FILTER (
            ALL ( 'Product'[Product Name], 'Product'[Product ID], 'Product'[Brand] ),
            'Product'[Brand] IN DISTINCT ( 'Brand slicer table'[Brand] )
        ),
        [Total unit sales:], DESC
    )
VAR _nonfocus =
    EXCEPT (
        ALL ( 'Product'[Product Name], 'Product'[Product ID], 'Product'[Brand] ),
        _topfivefocus
    )
VAR _topfivenonfocus =
    TOPN ( 5, _nonfocus, [Total unit sales:], DESC )
RETURN
    CALCULATE ( [Total unit sales:], KEEPFILTERS ( _topfivefocus ) )
        + CALCULATE ( [Total unit sales:], KEEPFILTERS ( _topfivenonfocus ) )

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

Hi @msmays5 
This is a 2nd solution where no tables shall be added. However this shall require to create a new calculated column in the Sales table (ProductName) to use it to slice by the table visual.
Other than my 2 solutions and the one provided by @Jihwan_Kim, it is not possible. The reason is a property of the DAX SUMMARIZECOLUMNS of the Power Bi visuals called Auto-Exist which aims to optimize calculations. With this property, once a column in a table is filtered then the complete table is filtered inside the SUMMURIZECOLUMNS. This is why the slicer table must be different than the slicing by table in the table visual.

 

I still recommend going for my first solution where you have a Connectd Brand Table. It is an extremely tiny table. Following this solution, the original report along with it's measure won't be affected at all and brand filter will continue working as normal.

1.png

Top 10 Products = 
VAR ProductValues = 
    CALCULATETABLE ( VALUES ( 'Sales'[ProductName] ), ALL ( 'Product' ) )
VAR SelectedProducts =
    CALCULATETABLE ( VALUES ( 'Sales'[ProductName] ), ALLSELECTED ( ) )
VAR Top5Selected = 
    TOPN ( 5, SelectedProducts, CALCULATE ( [Unit Sales], ALL ( 'Product'[BrandName] ) ) )
VAR AllNotSelected = 
    FILTER ( ALL ( 'Sales'[ProductName] ), NOT ( 'Sales'[ProductName] IN SelectedProducts ) )
VAR Top5AllNotSelected =
    CALCULATETABLE ( TOPN ( 5, AllNotSelected, [Unit Sales] ), ALL ( 'Product' ) )
VAR FilterTable = UNION ( Top5Selected, Top5AllNotSelected )
VAR Result =
        CALCULATE ( 
            [Unit Sales],
            ProductValues,
            FilterTable,
            ALL ( 'Product' )
        )
RETURN
    Result

Sample file here https://we.tl/t-YhVPgZVAli

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

Hi @msmays5 
My solution is pretty similar to @Jihwan_Kim's except the the newly created brand table is connect therefore it still can filter the other measures and visuals.

1.png2.png

Top 10 = 
VAR ProductValues = 
    CALCULATETABLE ( VALUES ( 'Product'[Product Name] ), ALL ( Brands ) )
VAR SelectedProducts =
    CALCULATETABLE ( VALUES ( 'Product'[Product Name] ), ALLSELECTED ( ) )
VAR Top5Selected = 
    TOPN ( 5, SelectedProducts, [Sales Amount] )
VAR AllNotSelected = 
    FILTER ( ALL ( 'Product'[Product Name] ), NOT ( 'Product'[Product Name] IN SelectedProducts ) )
VAR Top5AllNotSelected =
    CALCULATETABLE ( TOPN ( 5, AllNotSelected, [Sales Amount] ), REMOVEFILTERS ( ) )
VAR FilterTable = UNION ( Top5Selected, Top5AllNotSelected )
VAR Result =
    SUMX ( 
        ProductValues,
        CALCULATE ( 
            [Sales Amount],
            ALL ( Brands ),
            FILTER ( 
                FilterTable,
                'Product'[Product Name] = EARLIER ( 'Product'[Product Name] )
            )
        )
    )
RETURN
    Result 
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

 

Jihwan_Kim_2-1677295434974.png

 

 

Jihwan_Kim_1-1677295425808.png

 

Top five focus brand and top five nonfocus brand: =
VAR _topfivefocus =
    TOPN (
        5,
        FILTER (
            ALL ( 'Product'[Product Name], 'Product'[Product ID], 'Product'[Brand] ),
            'Product'[Brand] IN DISTINCT ( 'Brand slicer table'[Brand] )
        ),
        [Total unit sales:], DESC
    )
VAR _nonfocus =
    EXCEPT (
        ALL ( 'Product'[Product Name], 'Product'[Product ID], 'Product'[Brand] ),
        _topfivefocus
    )
VAR _topfivenonfocus =
    TOPN ( 5, _nonfocus, [Total unit sales:], DESC )
RETURN
    CALCULATE ( [Total unit sales:], KEEPFILTERS ( _topfivefocus ) )
        + CALCULATE ( [Total unit sales:], KEEPFILTERS ( _topfivenonfocus ) )

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi @Jihwan_Kim , thank you for the response. I'd like to do this without a disconnected table, as there are other product columns that might also be filtered. (I was trying to simplify the example). I would think this could be done without the disconnected table by using ALL/REMOVEFILTERS somewhere. Any thoughts?

Hi,

Sorry but it is quite difficult for me to understand the full datamodel without seeing it.

Do you want to select a specific Brand in the slicer, but you want to show all brands, and the slicer table is connected?

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim Here is my file. I was able to implement like you showed with a disconnected table, and it worke. However, I'm wondering if there is any way to do this without an extra table. Ideally, I'd like to use the Product[BrandName] in the Slicer and have it work without creating an extra table for the slicer. But if this isn't possible, your solution works. Thanks!

Hi @msmays5 
This is a 2nd solution where no tables shall be added. However this shall require to create a new calculated column in the Sales table (ProductName) to use it to slice by the table visual.
Other than my 2 solutions and the one provided by @Jihwan_Kim, it is not possible. The reason is a property of the DAX SUMMARIZECOLUMNS of the Power Bi visuals called Auto-Exist which aims to optimize calculations. With this property, once a column in a table is filtered then the complete table is filtered inside the SUMMURIZECOLUMNS. This is why the slicer table must be different than the slicing by table in the table visual.

 

I still recommend going for my first solution where you have a Connectd Brand Table. It is an extremely tiny table. Following this solution, the original report along with it's measure won't be affected at all and brand filter will continue working as normal.

1.png

Top 10 Products = 
VAR ProductValues = 
    CALCULATETABLE ( VALUES ( 'Sales'[ProductName] ), ALL ( 'Product' ) )
VAR SelectedProducts =
    CALCULATETABLE ( VALUES ( 'Sales'[ProductName] ), ALLSELECTED ( ) )
VAR Top5Selected = 
    TOPN ( 5, SelectedProducts, CALCULATE ( [Unit Sales], ALL ( 'Product'[BrandName] ) ) )
VAR AllNotSelected = 
    FILTER ( ALL ( 'Sales'[ProductName] ), NOT ( 'Sales'[ProductName] IN SelectedProducts ) )
VAR Top5AllNotSelected =
    CALCULATETABLE ( TOPN ( 5, AllNotSelected, [Unit Sales] ), ALL ( 'Product' ) )
VAR FilterTable = UNION ( Top5Selected, Top5AllNotSelected )
VAR Result =
        CALCULATE ( 
            [Unit Sales],
            ProductValues,
            FilterTable,
            ALL ( 'Product' )
        )
RETURN
    Result

Sample file here https://we.tl/t-YhVPgZVAli

Thank you for the thorough explanation. I will go with the table option.

Hi,

Thank you for your file. I think the disconnected table is one of the optiops to go for the solution. I am not sure, but I don't think Brand column that comes from the same table as Product column does work in this case.

Other way is to create a separate Brand table and create a relationship with product table, and then use Brand table - Brand column as a slicer.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Let me provide a sample file tomorrow morning to make this is as easy as possible to understand. Thank you again!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.