Rank top 2 of several columns

[Anonymous]

Hi there,

Let's say that i have the following data:

dimension	c1	c2	c3	c4
dim	1	2	5	8
dim	1	3	5	9
dim	0	2	6	8

How can i produce a bar chart, that shows, for the dimension, the highest average columns? In this case, the chart would display two bars, the average of C3 and the average of C4.

My guess is that, for a bar chart, the dimension would be the column dimension, and i would have two calculated measures, but i'm unsure what the formula for these would be.

Thanks for the help.

[swuehl · Accepted solution]

An option would be to use a CROSSTABLE LOAD prefix to transform your data:

CROSSTABLE (Cs, Value)

LOAD * INLINE [

dimension,c1, c2,c3, c4

dim, 1,2, 5, 8

dim, 1,3, 5, 9

dim, 0,2, 6, 8

];

Then create a chart with dimensions dimension and Cs and a single expression

=Avg(Value)

Then limit your second dimension to show only top 2 expression values.

[Anonymous]

Hi,

That works great for the example that i provided, thank you. Just a quick question, can i sort the bar graph from highest to lowest average?

Thanks.

[swuehl]

You mean, using multiple dimension values?

So you want to show top 2 Cs per dimension value, then sort the complete chart descending?

I don't think you can get this with two dimensions, but maybe with a single calculated dimension and an expression that filters the Top2.

Dimension:

=dimension & ' - ' & Cs

Expression:

=Only(Aggr(If(Rank(Avg(Value),4)<=2, Avg(Value)),dimension,Cs))

[Anonymous]

Thanks. Using only Cs as a dimension also seems to work.