Top 5 with Other Column

[Anonymous]

Hi Team,

Need DAX help!

I am looking for Top5 customer and their top unit in column.Apart from the Top 3 units which are in column I want to add all sales in Other column and then a Total Sales column. I am trying to create dax function but its not working.

Below is the sample data set

Customer	Unit	Sales
AA	SCM	500
AB	Procurment	600
AC	Sales	300
AD	Field	209
AA	Marketing	948
AB	Procurment	305
AZ	Marketing	382
AF	Services	633
AK	Field	993
AN	Procurment	244
AD	Field	362
AK	Marketing	750
AD	Procurment	250

Expected Output would be like

Top5 customer in row, Top3 unit in column(having the sales value for that unit) and "other" column (Having sales sum except the top3 value sale) and Grand total(Sum of all sales) at the end.

Customer	Field	Marketing	Procurment	Other	Grand Total
AA		948		500	1448
AB			905		905
AD	571		250	250	821
AF				633	633
AK	993	750			1743

Requesting you to do the needful.

Regards

Uphar

[Greg_Deckler]

@Anonymous - Take a look at the Complex Filter, it has a matrix example and similar use case to what you are looking to do. Not exact, but similar. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

[Anonymous]

Hi @Greg_Deckler 

I am able to fetch the top 3 unit using below DAX and top5 suppliers by using filter and TopN option. The only issue is with Other column (In which I need sum of all sales except those values which are available in top3 units). 

If you can help me how I can create other column in matrix. It would be a great help.

Top5 =

VAR __topN = 3

VAR __Unit =

RANKX (

ALLSELECTED ( Table [Sales]),

CALCULATE(

[Total Sales,

ALLEXCEPT(Table, Table[Unit] ) ),

,

DESC,

DENSE

) <= __topN

RETURN

IF( __unit, [Total_sales] )

How to add other column in matrix without disturbing the above DAX.

Regards

Uphar