Top 10 products

[sanjujeeboy]

Hi

My requirement is to get Top 10 values for Product A based on Sales. So there will be 10 rows only.

2nd requirement is when i select on country,eg US it should show top 10 sales values for US.

can both requirements be written in 1 expression.?

Manufacturer	Country	BU_OCR	Sales
Product A	US	Category 1	90584
Product A	UK	Category 6	88827
Product A	US	Category 2	54832
Product A	US	Category 4	49029
Product A	US	Category 2	42627
Product A	DE	Category 5	40113
Product A	US	Category 1	39711
Product A	US	Category 1	36775
Product A	DE	Category 1	36645
Product A	US	Category 2	33170
Product A	DE	Category 3	32970
Product A	UK	Category 1	32021
Product A	US	Category 2	30355
Product A	DE	Category 2	30100
Product A	UK	Category 3	29917
Product A	US	Category 4	25930
Product A	DE	Category 3	25696
Product A	US	Category 2	24523
Product A	US	Category 5	24485

[mahaveerbiraj]

Hi sanju ,

I hope below expression will help you ,

if(GetSelectedCount (Country)>0,sum(Sales),sum( {< Manufacturer={"Product A"}>} Sales))

and  you can sort by sales with desc 

let me know if  you are not clear.

Thanks 

Mahaveer 

[sanjujeeboy]

Hi Mahaveer,

for 1st requirement, when i put this condition,sum( {< Manufacturer={"Product A"}>} Sales)), it shows me more than 10 records. i want to show only top 10 based on sales irresepective of country and category. so my output should be like this:

Manufacturer	Country	BU_OCR	Sales
Product A	US	Category 1	90584
Product A	UK	Category 6	88827
Product A	US	Category 2	54832
Product A	US	Category 4	49029
Product A	US	Category 2	42627
Product A	DE	Category 5	40113
Product A	US	Category 1	39711
Product A	US	Category 1	36775
Product A	DE	Category 1	36645
Product A	US	Category 2	33170

can you share the formula for achieving this or may be qvf..

[sunny_talwar]

Try this may be

Sum(Aggr(If(Rank(TOTAL Sum({<Manufacturer = {'Product A'}>}Sales)) < 11, Sum({<Manufacturer = {'Product A'}>}Sales)), Manufacturer, Country, BU_OCR))

[mayuringale25]

Hi @sanjujeeboy 

Go to Table properties 

1.Select Dimension

2.Click on Limitation and select Fixed number.

3. Select Top and enter 10 and It will show you Top 10 Product By sales