Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Top 5 with Other Column

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

CustomerUnitSales
AASCM500
ABProcurment600
ACSales300
ADField209
AAMarketing948
ABProcurment305
AZMarketing382
AFServices633
AKField993
ANProcurment244
ADField362
AKMarketing750
ADProcurment250

 

 

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.

 

CustomerFieldMarketingProcurmentOtherGrand Total
AA 948 5001448
AB  905 905
AD571 250250821
AF   633633
AK993750  1743
      

 

Requesting you to do the needful.

 

Regards

Uphar

 

 

Top5 with Other column

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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