Help on Rank function

[#1]

Hi,

Can somebody help me with the rank function in the below scenario.

I have a crosstable with the following columns:

code	MeasureType	Value
ABC	Turnover	3000
ABC	Marketshare	3000
ABC	Ranking	3000
PQR	Turnover	2500
PQR	Marketshare	2500
PQR	Ranking	2500
XYZ	Turnover	3200
XYZ	Marketshare	3200
XYZ	Ranking	3200

I am looking to provide the following information for code ABC:

MeasureType	Value
Turnover	3000
MarketShare	34.48 (3000/(3000+2500+3200)*100
Ranking	2 (3200 is 1st, 3000 is 2nd and 2500 is 3rd)

With Measure type as dimension i am able to acheive Turnover and Marketshare. but not able to acheive the Ranking column. can somebody help me with this.

Thanks in advance

[swuehl · Accepted solution]

I think it could be just

rank(sum(Value))

I used code and MeasureType as dimensions and a pick(match()..) combination as expression to create the measures per MeasureType. Please see also attached.

[#3]

thanks swuehl....it work for me...but cant figure out how you made the dimension (MeasureType) as columns...

[swuehl]

It's a pivot table with the dimension pivoted to the top:

Click on "MeasureType" in the graph and move your mouse around while keeping button pressed. You'll see a blue line indicating the new position of the dimension.