Determine the RANK of a dimension not in Chart

[#1]

Let's assume a simple source table like this:

COUNTRY	CAR	SALES
DE	Car_1	1
DE	Car_2	2
FR	Car_1	4
FR	Car_2	3

From that I would like to derive a straight table that lists the COUNTRY and

1. who sells most (Top Seller)

2. what's the specific rank for a certain car, e.g. Car_1

Hence the result should look like this:

COUNTRY	Top_Seller	Rank of Car_1
DE	Car_2	2
FR	Car_1	1

Currently I am struggeling to find the correct expressions for these two columns. (In reality there is also a time dimension, that need to be aggregated to determine the rank.)

[ramoncova06]

this should help

[sunny_talwar]

This:

Expression 1: =FirstSortedValue(CAR, -SALES)

Expression 2: =If(Sum({<CAR = {'Car_1'}>} SALES) > 0, Rank(Sum(SALES), 4))

HTH

Best,

Sunny

[#4]

Thank you very much RamonCova06‌ & sunindia‌. Your solutions work fine for the first issue, but when I extend the data a little bit it unfortunately fails for the second. I attached my demo file with a little bit more enhanced data. Maybe someboy has an idea how to obtain the expected values in the third column (RANK OF BRAND B).

[swuehl · Accepted solution]

Try an expression like

=only({<BRAND = {B}>} aggr( rank(sum( UNITS)), COUNTRY, BRAND))

[sunny_talwar]

You are damn fast.

I was working my way to the solution, but you got there so fast. I hope to be like you one day

Thanks for sharing your knowledge swuehl‌

Best,

Sunny

[#7]

swuehl‌ & sunindia‌ Thank you very much for your quick replies and effort !!