Confused about how to use ranking to display a result in a text box

[Newguy]

Hi, this is my first post here and I have spent the afternoon trying to find my solution in previous posts without success.

Start with this screen-print:

I have at the moment two sub topics in my survey data (intending to add more later). In this table I have added two measures. Firstly, for each, what does the grey "-" represent? I have no gaps in the data or missing or misspelt sub topics (for example).

Secondly, I want to use a text box which states, for example, "The biggest average difference was Future Thinking". To achieve this I found this formula in my searches:

=only(if(aggr(Rank(Avg([Stdev Score Are]),4),[QI])=1,[Sub Topic]))

The problem is that no result appears in the text box when I add that formula as a Measure. Oddly, even if I exactly copy one of the examples from the table above as a new measure inserted next to my text, still nothing appears.

I am completely confused and have wasted hours. Please can the community give me some pointers?

[sunny_talwar · Accepted solution]

Try this

=Only({<[Sub Topic] = {"=Rank(Avg([Stdev Score Are]), 4) = 1"}>} [Sub Topic])

[Newguy]

Excellent, that works, thank you.

Firstly, is there a resource you can point me to that helps me understand the syntax?

Secondly, in the table in my original post above, as you can see there are some null values. In the options for the table I found the "Include Null" tickbox - removing that made it work as expected.

The trouble now is that if I change your formula to find the 2nd ranked item, it does bring through the null result. I realise I need to look into where the null field originates from, but in the meantime is it possible to update the formula to ignore nulls?

Thank you very much indeed for your time.

[sunny_talwar · Accepted solution]

Can you try this to see if there are more than 1 value for rank = 2 may be

=Concat(DISTINCT {<[Sub Topic] = {"=Rank(Avg([Stdev Score Are]), 4) = 1"}>} [Sub Topic], ', ')

[Newguy]

Hi Sunny

Thank you. I tried this and there was no result.

[sunny_talwar]

May be share a sample to show the issue.

[Newguy]

Hi Sunny

I updated my dataset and found indeed there were a few cases where the rank 2 picked up more than one row. Your two formulae have helped me finish and I am very very grateful. Thank you very much.