First Sorted Value against a measure

[JonathanKelly]

Using the below formula I can get the first sorted value across all the data. 

=FirstSortedValue([Type], -aggr(avg([Weeks]),[Weeks]))

However, within a table I am trying to colour the first sorted value of the Type when the avg([Weeks]) is within specific ranges.

I have multiple week lengths against each type as such I am using the average of the Weeks field. Putting an if statement before the above formula errors out when stating that I only want the avg([Weeks]) to be less than 51.

The ranges I am using are 0-50(red), 50-100(yellow)and above 100(green) 

i.e if I had calculated avg weeks of Car=20 weeks, Bus=49 weeks, Bike=30 weeks, Plane=100 weeks, Boat=73 weeks, Walk=109 weeks, Rollerblade=150 weeks. Car, bus and bike are in the red range, plane and boat are in the yellow range, Walk and Rollerblade are in the green range. I want Bus to highlight red, Plane to highlight yellow and rollerblade to highlight green. 

using:

=if(Avg([Weeks])<'53', rgb(255,0,0)) this highlights all three of Car, Bus and Bike. instead of just the top value

[Aasir]

You can try below,

1st Create variables for your color ranges
RedLimit: 50;
YellowLimit: 100;
 

Then use the existing expr
FirstSortedValue([Type], -aggr(avg([Weeks]), [Weeks]))

Then Check color login

=if(
Avg([Weeks]) <= $(RedLimit), rgb(255, 0, 0),
Avg([Weeks]) <= $(YellowLimit), rgb(255, 255, 0),
Avg([Weeks]) > $(YellowLimit), rgb(0, 255, 0)
)

Finally color by expression
=if(
FirstSortedValue([Type], -aggr(avg([Weeks]), [Weeks])) = [Type],
if(
Avg([Weeks]) <= $(RedLimit), rgb(255, 0, 0),
Avg([Weeks]) <= $(YellowLimit), rgb(255, 255, 0),
Avg([Weeks]) > $(YellowLimit), rgb(0, 255, 0)
)
)

[JonathanKelly · Accepted solution]

Thank you for the breakdown, I couldn't get it to work that way, it may be a way the data is formatted in excel.

I managed to do it using the Rank function versus FirstSortedValue as it is already referenced against Type I can just use the avg(Weeks). Since I wanted the top 3 I just repeated for each rank.

=if(

rank(if(avg([Weeks])<= $(vRedLimit) ,avg([Weeks])))=1 or

rank(if(avg([Weeks])<= $(vRedLimit),avg([Weeks])))=2 or

rank(if(avg([Weeks])<= $(vRedLimit),avg([Weeks])))=3, rgb(255,0,0),

if(

rank(if(avg([Weeks])> $(vRedLimit) and avg([Weeks])<= $(vGreenLimit) ,avg([Weeks])))=1 or

rank(if(avg([Weeks])> $(vRedLimit) and avg([Weeks])<= $(vGreenLimit),avg([Weeks])))=2 or

rank(if(avg([Weeks])> $(vRedLimit) and avg([Weeks])<= $(vGreenLimit),avg([Weeks])))=3, rgb(255,255,0),

if(

rank(if(avg([Weeks])> $(vGreenLimit) ,avg([Weeks])))=1 or

rank(if(avg([Weeks])> $(vGreenLimit),avg([Weeks])))=2 or

rank(if(avg([Weeks])> $(vGreenLimit),avg([Weeks])))=3, rgb(0,255,0)

)

)

)