
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
First Sorted Value against a measure
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
)
)
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
)
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
)
)
)
