topic Re: Top N and Others in DAX Commands and Tips

Top N and Others

Suzy123 — Sat, 17 Sep 2022 06:48:42 GMT

Hi all,

I am doing a top 5 plus others for my report, and it works fine until i put the month in the columns it will show top 5 for each month and Total column will only add the Top 5 based on the total result.

The measure I created is as below, and this is how it looks before I put the Month in the Columns:

This is how it looks after I put the Month in the columns:

Does anyone has any idea how to resolve this... it seems so hard, and the result I want is just

Mesure:

Top N Sum Sales = VAR TopSalesTable = TOPN ( 5, ALLSELECTED ('Unique Customers'), [Total Sales] ) VAR TopSales = CALCULATE ( [Total Sales], KEEPFILTERS ( TopSalesTable ) ) VAR OtherSales = CALCULATE ( [Total Sales], ALLSELECTED ('Unique Customers' ) ) - CALCULATE ( [Total Sales], TopSalesTable ) VAR CurrentProd = SELECTEDVALUE ( 'Unique Customers'[Customer name] ) RETURN IF ( CurrentProd <> "Others", TopSales, OtherSales )

Re: Top N and Others

Jihwan_Kim — Sat, 17 Sep 2022 07:31:50 GMT

Hi,

I tried to simplify the sample and created a new sample pbix file like below. (top3 and others)

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 data model.

Top 3 sales and others: = VAR _topthreecustomerlistwholeyear = TOPN ( 3, ALLSELECTED ( Customer[Customer] ), CALCULATE ( [Sales total:], REMOVEFILTERS ( 'Calendar' ) ), DESC ) VAR _topthreefilter = TOPN ( 3, ALLSELECTED ( Customer[Customer] ), [Sales total:], DESC ) VAR _allsales = CALCULATE ( [Sales total:], REMOVEFILTERS ( Customer ) ) VAR _othersales = _allsales - CALCULATE ( [Sales total:], _topthreefilter ) RETURN SWITCH ( TRUE (), HASONEVALUE ( Customer[Customer] ), IF ( SELECTEDVALUE ( Customer[Customer] ) = "Others", _othersales, CALCULATE ( [Sales total:], KEEPFILTERS ( Customer[Customer] IN _topthreecustomerlistwholeyear ) ) ), [Sales total:] )

Re: Top N and Others

Suzy123 — Mon, 19 Sep 2022 05:32:28 GMT

Appreciate your help!! Genius! It works now 🙂