Ranking in pivottable

[guyvermeiren]

Hello,

I have a pivot table with 3 dimensions and subtotals (partial sum) for each dimension (payer, goed.groep and productline).

I would like to show only the top 10 for my payers so I need to determine the rank for the total by payer.

Now if I use = Rank(Sum( {$<[Year]= {$(=max([Year])-1)},Period= {'*'} >} [Turnover] ),0)

it always ranks on the most expanded dimension

Any solution for showing only the top 10 ??

Thanks

Kind regards,

Guy

[matt_crowther]

Guy,

I may be over simplifying this but I've created a simple 3 dimension Pivot and the below works for me when it comes to ranking across all values:

=rank(total sum(Turnover))

You can then of course add an 'if' to the expression to limit it to the top 10 only.

Hope that helps,

Matt - Visual Analytics Ltd

[guyvermeiren]

Matt,

Is it used in a calculated dimesion ?? Or is it placed as expression.

I don't know the correct syntax for Total. I can't find any information on that

Thanks anyway

Kind regards,

Guy

[Miguel_Angel_Baeyens]

Hello Guy,

Check this post and the attached file.

Hope that helps.

[guyvermeiren]

Hello Miguel Angel,

I understand your solution in the post and tried to use it. I can create the rank in my dimension, but when I copy the expression and combine it with an ' If ' in the calculated dimension I get //Error in calculated dimension.

My expression is:

= rank(Sum( {$<[COPA.Fiscal Year]= {$(=max([Calendar.Year])-1)},COPA.Period= {'*'} >} [COPA.Niko Act Gross Sales incl Projects Rebates NL/FR]))

And my calculated dimension (where I want to check "Suppress when Value is Null"):

= if(rank(Sum( {$<[COPA.Fiscal Year]= {$(=max([Calendar.Year])-1)},COPA.Period= {'*'} >} [COPA.Niko Act Gross Sales incl Projects Rebates NL/FR]))<=10,[GeneralPayerMasterData.Customer Name&Code],Null())

What did I do wrong ? Please help.

Thanks

Kind regards,

Guy

[Miguel_Angel_Baeyens · Accepted solution]

Hello Guy,

to get the values you want in the dimension you need to aggregate the rank expression by the other dimensions in the chart, so your final calculated dimension looks like

=if(Aggr(rank(Sum( {$<[COPA.Fiscal Year]= {$(=max([Calendar.Year])-1)},COPA.Period= {'*'} >} [COPA.Niko Act Gross Sales incl Projects Rebates NL/FR])), [GeneralPayerMasterData.Customer Name&Code])<=10,[GeneralPayerMasterData.Customer Name&Code],Null())

Hope that helps.