How to identify largest category and value?

[Anonymous]

I have a list of products, A - X that are sold at various prices.  My table consists of records of each sale:

Sales Table:

Product          Location          Amount

A                    OK                    4.56

A                    LA                     12.45

B                    KS                    5.00

B                    MI                      3.75

... and so on.

I have a pareto chart drawn, but I want a Text Box that says (for example):

Top Product

Product A, $25 total spend

I am able to get Product A using:

=FirstSortedValue([Product],-aggr(sum([Amount]),[Product]))

But I don't know how to pull the summarized value for the top product.  In other words, I can generate the Top Product = Product A, but I can't generate the $25 Total Spend part of the summary.

Any ideas?

[ychaitanya · Accepted solution]

Try Below in your expression

= FirstSortedValue([Product],-aggr(sum([Amount]),[Product]))

&

Sum(

{

<

PRODUCT={"$(=FirstSortedValue([Product],-aggr(sum([Amount]),[Product])))"}

>

}

[Amount]

&

'Total Spend'

[Anonymous]

I see what you did there, but it didn't work.  I got - for the whole thing, although no syntax errors

[ychaitanya]

i did tried with my data it worked .. just check the labels

[Anonymous]

Ok, we were missing a closing parenthesis ) after the last [Amount].  Don't know why it didn't flag that as a syntax error, but once that was fixed it worked fine.  Thank you!

[ychaitanya]

Glad . It worked.. !

Please close the thread by marking the Correct Answers