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

[msmays5]

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

[Jihwan_Kim · Accepted solution]

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.

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

[tamerj1]

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.

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 · Accepted solution]

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.

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

[msmays5]

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?

[Jihwan_Kim]

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?

[msmays5]

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

[tamerj1 · Accepted solution]

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.

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

[msmays5]

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

[Jihwan_Kim]

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.

[msmays5]

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