How to show top 3 and bottom 3 sales in textbox?

[Akina0929]

Hi community,

           I have country as dimension and sales as expression. I want to show top 3 and bottom 3 sales in Textbox.

Please help me .

Regards

Anji

[swuehl]

Maybe something like

=Sum({<Country = {"=Rank(Sum(Sales))=1"}>}Sales) // top sales

=Concat({<Country = {"=Rank(Sum(Sales))=1"}>} DISTINCT Country,', ') // top country

=Sum({<Country = {"=Rank(-Sum(Sales))=1"}>}Sales) // bottom sales

=Concat({<Country = {"=Rank(-Sum(Sales))=1"}>} DISTINCT Country,', ') // bottom country

adapt 1 to 2,3 for second, third ...

edit: used Concat() to show ties.

[micheledenardi · Accepted solution]

='Highest Sales' &CHR(13)&CHR(13)&

FirstSortedValue(Country, -aggr(sum(Sales),Country)) &': '& sum({<Country={'$(=FirstSortedValue(Country, -aggr(sum(Sales),Country)))'}>}Sales) & chr(13) &

FirstSortedValue(Country, -aggr(sum(Sales),Country),2)&': '& sum({<Country={'$(=FirstSortedValue(Country, -aggr(sum(Sales),Country),2))'}>}Sales) & chr(13) &

FirstSortedValue(Country, -aggr(sum(Sales),Country),3)&': '& sum({<Country={'$(=FirstSortedValue(Country, -aggr(sum(Sales),Country),3))'}>}Sales)

='Lowest Sales' &CHR(13)&CHR(13)&

FirstSortedValue(Country, +aggr(sum(Sales),Country)) &': '& sum({<Country={'$(=FirstSortedValue(Country, +aggr(sum(Sales),Country)))'}>}Sales) & chr(13) &

FirstSortedValue(Country, +aggr(sum(Sales),Country),2)&': '& sum({<Country={'$(=FirstSortedValue(Country, +aggr(sum(Sales),Country),2))'}>}Sales) & chr(13) &

FirstSortedValue(Country, +aggr(sum(Sales),Country),3)&': '& sum({<Country={'$(=FirstSortedValue(Country, +aggr(sum(Sales),Country),3))'}>}Sales)

[senpradip007]

Use FirstSortedValue to retrieve top / bottom country based on Sales.

Try like:

Top:

sum({<Country={'$(=FirstSortedValue(Country, -aggr(sum(Sales),Country)))'}>}Sales)

Bottom:

sum({<Country={'$(=FirstSortedValue(Country, aggr(sum(Sales),Country)))'}>}Sales)

[Akina0929]

Hi Michele,

    Thank you very much for your Replay.

Regards

Anji

[micheledenardi]

You're welcome!

[varma8998]

Hi ,

if I am calculating direct sum (sales) I am getting value

but the problem is we have to take few filters from each column to calculate the sales

example

columns are Curreny, Scenario, Code, Year, Sales_Amount we have columns like this

Column Values Curreny(USD), Scenario(forecast), Code(1apsjc), Sales_Amount