Display Total of Top 5 Customers and other data

[CB308]

Hi,

I have a chart that shows the following:

Store	Value	Rank
1	745	1
2	580	2
3	560	3
4	550	4
5	525	5
6	515	6
7	490	7
8	480	8
9	400	9
10	390	10
Total	5235

I would like to display in a combo chart the following from the above data:

Top 5 Stores - Value $2,960

Average of All Stores - $524

Value for individual store selected for comparrison (store 9) $400

Can this be done and if so, any assistance would be much appreciated.

[pover]

The combo chart would have 3 expressions.

The first would be something like this:

sum({$<Store={"=rank(sum(Value))<=5"}>} Value)

avg(aggr(sum({$<Store=}>} Value),Store))

sum(Value)

Regards.

[CB308]

thanks for your prompt response!

for the second expression, avg(aggr(sum({$<Store=}>} Value),Store)). I am receiving the error code.... error in expression

When I use your first and third expression and select "one store", only the third expression displays

any thoughts?

[pover · Accepted solution]

Here are the expression with some modifications:

sum({$<Store={"=rank(total sum({$<Store=>} Value))<=5"}>} Value)

avg(aggr(sum({$<Store=>} Value),Store))

The Store= should ignore the selection of one store.

Regards.

[CB308]

Awesome! thanks so much...

If I wanted the average of all stores regardless of when selecting only my store, what would that calculation be?

Sorry, I'm new to set analysis.

avg(aggr(sum({$<Store=>} Value),Store))

[pover]

It can be a little confusing because each nested function has its own set analysis. Try this expression.

avg({$<Store=>} aggr({$<Store=>} sum({$<Store=>} Value),Store))

Regards.

[CB308]

worked perfectly!

thank you for your time!!!!