Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Top 5 By Two different Conditions

Hi Everyone,   I have a set of data given below. Iwant top 5 assoicate name by spend as well by division. For example the division (Top5 would be in Column) and associate name would be in Rows in M...
Top N
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    I've attached a file with a solution.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

This appears to be what you are going for?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

    Thank you for your suggestion.

     

    Need top 5 also Division in column and overall total in the end. In your visualisation I can see 6 division. And in my final data I have lots of other division. 

     

    Regards

    UPhar

    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      This one was tricky! (to do this inside a matrix visual).  Here is one way to do it.  The matrix matches your original visual (excep the total).  I broke it up into pieces/variables to make it easier to follow/adjust.  I ran out of time, but the you can it one step further to also get the total showing correct (i.e., sumx(values(SpendTable[Division]), [Top 5 Div and Assoc]), and use that as the measure instead).  Not confirmed but that should work.

       

      Top 5 Div and Assoc = var selecteddiv = SELECTEDVALUE(SpendTable[Division])
      var selectedassoc = SELECTEDVALUE(SpendTable[Associate])
      var summarytable = ADDCOLUMNS(ALL(SpendTable[Division], SpendTable[Associate]), "Spend", [SpendTotal], "AssocRank", CALCULATE([AssociateRank], all(SpendTable), SpendTable[Associate]=EARLIER(SpendTable[Associate])),"DivRank", CALCULATE([DivisionRank], ALL(SpendTable), SpendTable[Division]=EARLIER(SpendTable[Division])))
      var filtered = Filter(summarytable, AND([AssocRank]<=5, [DivRank]<=5))
      var divfiltered = Filter(filtered, SpendTable[Division]=selecteddiv)
      var assocfiltered = FILTER(divfiltered, SpendTable[Associate]=selectedassoc)
      var finalresult = SUMX(assocfiltered, [Spend])
      return finalresult
       
      If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
      Regards,
      Pat