Show Selected plus 'Others'

[israrkhan]

Hi Community,

i have a pivot table, with two dimensions and one expressions..

my dimensions are:

1) =If(Aggr(Rank(Sum(SellOut.QTY)), Product)<=10, Product, 'Others')              // to show top 10 Product default

2) =Aggr(if(YearWeek > (max( TOTAL YearWeek) - 8), YearWeek), YearWeek)  // to show last 8 week default.

i have one expression...

1)  =if(Dimensionality()=0,SUM({<Product=>}SellOut.QTY),Sum(SellOut.QTY))

// to show the full total all the time, even if user has selected the products.

finally i see this graph.. which is correct...

now if i make selection in Product list box, let say i chose 5 Products...the chart look like below...

everything is ok, but only 'Others' row get disappear...

i want to show Selected and 'others' aswell..

how to show other all the time... all the time selected plus 'others' as a row..

i think its clear...

Many Thanks

[marcus_sommer]

I have tried to simulate this within an application and for me it looked thats worked:

[israrkhan]

it looks like its working for you...

can you please share a sample...?

[israrkhan]

Dear Marcus, i see in your image it seems it working for you,

but i tried same , but still no luck...

cam you please have a look on attached sample, in the document, see if i dont select any brand, then i can see top  10 brands and one row for others.

but i i select a brand then i cant see 'other'

please have a look on it...

Many Thanks...

Israr Khan

[marcus_sommer · Accepted solution]

Untill a proper sorting (which could be quite difficult within pivot-tables especially with calculated dimensions and a first try with dual() didn't work - but I think with some more efforts it should work, too - see for example: Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)) should it work now.

- Marcus