Forum Discussion

ngocnguyen's avatar
ngocnguyen
Icon for Helper IV rankHelper IV
4 years ago
Solved

CONCATENATE & TOP N DAX

Hi all

I have a input data as table 1. Now I want to create a matrix as table 2 with logic in Remark column as belows:
Finding top 3 staff have biggest Amount 
Finding top 3 model of staff that have biggest amount

Fill up in "Remark" column as logic:

Staff1 ( model 1_ Amount model 1, Model 2_Amount model 2, Model 3, Amount model 3),

Staff2 ( model 1_ Amount model 1, Model 2_Amount model 2, Model 3, Amount model 3),
Staff3 ( model 1_ Amount model 1, Model 2_Amount model 2, Model 3, Amount model 3),

Thanks in advance for your support!

 

 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, ngocnguyen ;

    Try it.

    rank = 
RANKX(ALLEXCEPT('Table','Table'[Manager]),CALCULATE(SUM([Amount]),ALLEXCEPT('Table','Table'[Manager],'Table'[Staff])),,DESC,Dense)
    TOP N = 
var _rank2=RANKX(ALL('Table'[Staff]),CALCULATE( SUM([Amount])),,DESC,Dense)
return IF(_rank2<=2,
         CONCATENATEX(
             FILTER( VALUES('Table'[Model]),RANKX(ALLSELECTED('Table'[Model]),CALCULATE( SUM([Amount])),,DESC,Dense)<=2),
              'Table'[Model],","))
    Measure = 
CONCATENATEX( SUMMARIZE(FILTER('Table',[rank]<=2),[Staff],"1",[Staff]&"("&[TOP N]&")"),[1],"|")

    The final output is shown below:

     


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    ngocnguyen , Try like

     

    Top 3= Var _tab = addcolumn(summarize( Table, Table[model]),"_1" , calculate([Revenue], TOPN(3,allselected(Table[model]),[Revenue],DESC), values(Table[model])) )
    return
    concatenateX(_tab, "model " & [_1] )

    • ngocnguyen's avatar
      ngocnguyen
      Icon for Helper IV rankHelper IV
      4 years ago

      amitchandak 
      Thank you, but, it's not working
      I just want to show top 3 amount of model  in top 3 amount of staff, then concatenate them together 

    • ngocnguyen's avatar
      ngocnguyen
      Icon for Helper IV rankHelper IV
      4 years ago

      hi CNENFRNL 
      Thank so much for your support. However, in the data table , could you add 1 column name " Manager"
      The Output I want is to find top 3 staff that have biggest Amt of each Manager, then find top 3 model have biggest Amt of each staff. After that, canculate these top 3 model
      Thanks

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, ngocnguyen ;

    You could try it.

    TOP N = 
var _rank= RANKX(ALL('Table'[Manager]),CALCULATE( SUM([Amount]),REMOVEFILTERS('Table'[Staff])),,DESC,Dense)
var _rank2=RANKX(ALL('Table'[Staff]),CALCULATE( SUM([Amount])),,DESC,Dense)
return IF(_rank<=3 && _rank2<=3,
         CONCATENATEX(
             FILTER( VALUES('Table'[Model]),RANKX(ALLSELECTED('Table'[Model]),CALCULATE( SUM([Amount])),,DESC,Dense)<=3),
              'Table'[Model],"|"))

    The final output is shown below:

     

    If not right , could you share the result  you want to output?
    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ngocnguyen's avatar
      ngocnguyen
      Icon for Helper IV rankHelper IV
      4 years ago

      HI v-yalanwu-msft 
      Thanks so much for your support.
      As your data is small, So I make another output as below:
      I wanna find top 2 of staff for each manager, then find top 2 model of each staff , then concate them

      • v-yalanwu-msft's avatar
        v-yalanwu-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, ngocnguyen ;

        Try it.

        rank = 
RANKX(ALLEXCEPT('Table','Table'[Manager]),CALCULATE(SUM([Amount]),ALLEXCEPT('Table','Table'[Manager],'Table'[Staff])),,DESC,Dense)
        TOP N = 
var _rank2=RANKX(ALL('Table'[Staff]),CALCULATE( SUM([Amount])),,DESC,Dense)
return IF(_rank2<=2,
         CONCATENATEX(
             FILTER( VALUES('Table'[Model]),RANKX(ALLSELECTED('Table'[Model]),CALCULATE( SUM([Amount])),,DESC,Dense)<=2),
              'Table'[Model],","))
        Measure = 
CONCATENATEX( SUMMARIZE(FILTER('Table',[rank]<=2),[Staff],"1",[Staff]&"("&[TOP N]&")"),[1],"|")

        The final output is shown below:

         


        Best Regards,
        Community Support Team_ Yalan Wu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.