Displaying an "x of y values" ranking within table

[Anonymous]

Hello Everyone,

We are interested in displaying an "x of y" ranking within a table, and I cannot seem to get it to work.

The scenario is this: Our company ("Alpha Company") has market information for us and for the competition.  Within a table, I would like to see Countries on rows and a measure of how we rank vs. the market within the same table.  I don't want to show the other Companies on this table.  Please note that I also don't want to actively select "Alpha Company"

Something akin to this:

The ranking is not the ranking of Countries, but the ranking of our Company against the other Companies within that Country.

My presumption is that I'm doing something wrong with the the placement of AGGR().  I thought I had a breakthrough by using ONLY() in conjunction, but it only evaluates once for the whole app instead of going Country by Country.

=ONLY( {<[Company] = {'Alpha Company'}>}AGGR( RANK(SUM(Sales),0,1),[Company])) & ' of ' & COUNT(DISTINCT [Company])

When I use the above, I get this:

It displays the correct number in the text box, and in any country where it just so happens to match (like Austria in the example).

Any help is appreciated.

Best,

Corey

[kogasawara]

try this

=only(if(Company='Alpha Company',aggr(RANK(SUM(Sales)),Country,Company))) & ' of ' & COUNT(DISTINCT [Company])

[kogasawara · Accepted solution]

or
if you want to use set analysis
=only({<Company={'Alpha Company'}>}aggr(RANK(SUM(Sales)),Country,Company)) & ' of ' & COUNT(DISTINCT [Company])

[Anonymous]

Many thanks!  This did the trick.  I had tried something like this in an earlier version, but on our actual table it's a cycle group.  I had tried GetCurrentField(CycleName) and it didn't like that so I moved on.  I didn't think to try the plain text version of the field name.

In order to get this to work with my cycle group, I had to add a variable and populate the variable with:

vCycleName = '['&GetCurrentField(CycleName)&']'

=ONLY( {<[Company] = {'Alpha Company'}>}AGGR( RANK(SUM(Sales),0,1),$(vCycleName),[Company])) & ' of ' & COUNT(DISTINCT [Company])