How to get the next top 10 products that excludes the top 5 products

[Anonymous]

Hi. 

I am trying to achieve the following problem:

2 graphs: 

First graph is top 5 products by sales volume and date. This is simple and I can just put my product column on the X axis with sales volume on Y axis (the values) I can then put my date on the legend, I can use the filter panel and select product --> Top5 by sales volume, done! 

Second graph:

What I have to work out is how would i get the next top ten from the remaining products that do not include the already found top 5 products.

I have tried the following logic below: Create a table that gets the top 5 prodcuts by sales.

I then create another table where I attempt to filter out these products so that when i try another TopN it ignores the top 5 products.

Top5Products = 
TOPN(
    5,
    Filter(SUMMARIZE(
        'MODEL DimProduct',
        'MODEL DimProduct'[ProductidID],
        'MODEL DimProduct'[ProductName],
        'MODEL DimProduct'[ProductFlag]
        "CountSales",
        count('MODEL FctSales'[SaleID])
    ),'MODEL DimProduct'[ProductFlag]= True()),
    [CountSales]
)

NotTop5Products = Filter('MODEL DimProduct', 
       Not('MODEL DimProduct'[ProductName]) in SELECTCOLUMNS(Top5Products,"name",Top5Products[ProductName] ))

This does not seem to work and shows a product in the graph that is already in the Top 5.

Maybe I am thinking too deep on this...

Many thanks! 

[Anonymous]

Hi, 
Does this work for you?

1. Use RankX to rank products
2. use filter pane to show 1-5 and 6-10.

Products 1-7 have data in 2022
product 8-11 have data in 2030, hence they show no sales due to the slicer.
You can naturally sort these away in the fitler pane saying: Sales > 0

[johnt75]

You could try taking the top 20 products and then taking the bottom 15 from that table, something like

Second Top 15 Products =
VAR summaryTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'MODEL DimProduct',
                'MODEL DimProduct'[ProductidID],
                'MODEL DimProduct'[ProductName]
            ),
            "Num sales", COUNTROWS ( 'Model FctSales' )
        ),
        'MODEL DimProduct'[ProductFlag] = TRUE ()
    )
VAR top20 =
    TOPN ( 20, summaryTable, [Num sales] )
VAR second15 =
    TOPN ( 15, top20, [Num sales], ASC )
RETURN
    second15

[Anonymous]

Thanks for your input, i am getting error: Argument '7' in SUMMARIZE function is required.

[johnt75]

I think you've got a trailing comma in the SUMMARIZE function

[Anonymous]

Thanks! Found it, the only other thing i have now realised is that I dont have a relationship with date in the new table and so i now cant use date as i did so easily in the first graph. Any advice on that? 

[johnt75]

That will be trickier. Calculated table like this are only calculated during data refresh, so they pay no attention to slicers or visual level filters, meaning that this table is calculating the second 15 products over all time, not limited to a particular date range.

If you wanted to use measures which only show the products from within the 2nd 15 you could do something like

Sales to 2nd 15 =
CALCULATE( [Sales Measure], TREATAS(VALUES('Second Top 15 Products'[ProductID]), 'Model DimProduct'[ProductID])

If you wanted the 2nd top 15 products to be calculated dynamically then you could create a table variable within a measure using the DAX code I posted, and then use those values as a filter to CALCULATE

[Anonymous]

Thanks again, though I think that last bit is a bit out of my depth... Is there any other way one can show a graph with the top 5 and then another graph that shows the top 10 from the remaining products excluding that top5? 

[johnt75]

you could try creating a relationship between the product ID columns on the 2 tables, and use the product ID from the summary table in your graph, but I think you might run into circular dependency problems

[Anonymous]

Hey, thanks for your reply. Yes I have attempted that and unfortunately there are circ deps.