Forum Discussion
DAX
Use calculate column instead of measure to achieve the rank:
MRank2 = RANKX ( FILTER ( 'Sales Data', 'Sales Data'[Dep] = EARLIER('Sales Data'[Dep]) ), RANKX ( ALL ( 'Sales Data' ), 'Sales Data'[Target],, DESC, Dense) )Then modify the measure like below:
Second lowest Quota = VAR lowest_count = COUNTROWS(FILTER('Sales Data', 'Sales Data'[MRank2] = 1)) RETURN CALCULATE(MAX('Sales Data'[Target]), FILTER(ALL('Sales Data'), 'Sales Data'[MRank2] = lowest_count + 1))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can you share example data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
I have shared below sample data table for reference
SalesManager SalesRep Dep Target Peter Andrew Prop1 210000 Peter Macman Prop1 280000 Peter Alex Prop1 210000 Peter Jack Prop1 78000 Peter Joe Prop1 120000 Peter peter Prop1 120000 Peter Adam Prop1 120000 Peter Stephanie Prop1 210000 Peter Kirsten Prop1 350000 Lenda Ajay Pro2 5000000 Lenda Rocky Pro2 3000000 Lenda Pratik Pro2 3500000 Lenda Harshal Pro2 1000000 Lenda Das Pro2 2500000 Lenda Vivek Pro2 1700000 Lenda Lina Pro2 1500000 Lenda Dhoni Pro2 1000000 Use calculate column instead of measure to achieve the rank:
MRank2 = RANKX ( FILTER ( 'Sales Data', 'Sales Data'[Dep] = EARLIER('Sales Data'[Dep]) ), RANKX ( ALL ( 'Sales Data' ), 'Sales Data'[Target],, DESC, Dense) )Then modify the measure like below:
Second lowest Quota = VAR lowest_count = COUNTROWS(FILTER('Sales Data', 'Sales Data'[MRank2] = 1)) RETURN CALCULATE(MAX('Sales Data'[Target]), FILTER(ALL('Sales Data'), 'Sales Data'[MRank2] = lowest_count + 1))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks it's working for me
