Forum Discussion
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...
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
Microsoft Employee
Microsoft EmployeeThis 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
Regards,
Pat