DAX Calculate Sum of sales per productid filter by productid ( NOT IN TOP 20 )

Question

I am fairly new to PowerBI DAX and I want to filter out the top 20 product ids in a measure.

I came up with this formula but it does not seem to be working and I was hoping to get some help here.

$ Amount Parcel = 
    CALCULATE(
        SUM(Data[$ Amount Parcel]),
        FILTER (Data, NOT (Data[idProduct], SUM(Data[NetSales])) IN TOPN(20, SUMMARIZE(Data, Data[idProduct], "NetSales", SUM(Data[NetSales]))))
  )

I want to show sales per PID for all products except for our 20 best sellers.

Thank you !!

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2019-11-19T04:20:53

I would suggest an easier approach adding a dimension column.

First of all, you need to have Product dimension table separated from Sales fact table. Make sure to create one-to-many relationship between Product and Sales with "Single" cross filter direction.

Then you can create a calculated column on Product table, which can be used to filter out top selling products.

Sales Rank = RANKX('Product', CALCULATE(SUM(Sales[SalesAmount])))

Now drag and drop Sales Rank field into the Filters pane of your visualization, and set the filter condition so that top selling products will not be shown.

DAX Calculate Sum of sales per productid filter by productid ( NOT IN TOP 20 )

1 Answers