In that case, you can try to use something like this:
=FirstSortedValue({<
[Audience Censor Rating]={"=Count(AudienceID) <= Fractile( total Aggr( Count(AudienceID), [Audience Censor Rating] ) , 2/3 )"}
>} total distinct [Audience Censor Rating], -[Censor Ranking])
There are 2 important things happening here:
- The Set Analysis shown above is using the Fractile() function, which allows you to tell the point in a distribution of values at which n% of values fall beneath it. In our case, we use this:
Fractile( total Aggr( Count(AudienceID), [Audience Censor Rating] ) , 2/3 )
...because it tells us the point at which 66.666% of Count(AudienceID) values grouped by [Audience Censor Rating] fall beneath. The rest of that set analysis, =Count(AudienceID) <=, is going to help us return only the Censor Ratings where the count of AudienceID fell into the lower 66.666%.
2. The other important part of this whole expression is the FirstSortedValue() function, which allows up to return a value in a field based on the another field after it's sorted. In our case, we use this:
=FirstSortedValue({<...>} total distinct [Audience Censor Rating], -[Censor Ranking])
...because we want to return the value of [Audience Censor Rating] after the [Censor Ranking] is sorted such that its highest number is used. We ensure that we get the highest [Censor Ranking] by putting the minus sign in front of it (otherwise it uses the lowest value). We use the total and distinct keywords to ensure that only get a single value returned and have it essentially disregard the chart's dimensions.
So ultimately, you can use a Background Color expression like this:
If([Audience Censor Rating] =
FirstSortedValue({<
[Audience Censor Rating]={"=Count(AudienceID) <= Fractile( total Aggr( Count(AudienceID), [Audience Censor Rating] ) , 2/3 )"}
>} total distinct [Audience Censor Rating], -[Censor Ranking])
, Magenta()
)
