Get top 10 customer with exclusion of one customer

[vsap2000]

Hi,

I am trying to develop a table which will have top 10 customer based on sale, which I can achieve by =Sum({<CustName = {"=Rank(Sum(Sale Amt))<=10"}>} Sale Amt) along with this user wants to exclude particular customer name so i added

Sum({<CustName = {"=Rank(Sum(Sale Amt))<=10"},

                 CustName -= {'Abc Com'} >} Sale Amt)

When I add the 2nd criteria, it gives me all customer and removes Abc Com customer. 

Is any way I can achieve top 10 customer excluding particular customer without changing any Custname field properties.

Thanks in Advance.

vsap2000

[Lisa_P]

You can limit to top 10 in your chart in your dimension.

So in your table, go to properties, Columns, CustName and Set the Limitation to Fixed Number, Top 10, deselect Show Others. Then in your Measure, remove the customer you don't want:

Sum({CustName -= {'Abc Com'} >}[Sale Amt])

[vsap2000]

Thanks for reply, I don't want to limit using properties as I have to use same expression for other calculation which changes my result, so if it is possible using both in set analysis that would be more helpful. I have already tried this but it changes my other result.

I would really appreciate if I can get result using Set Analysis.

Thanks in advance.

[Lisa_P · Accepted solution]

Ok, try this:

Sum({<CustName = {"=Rank(Sum(Sale Amt))<=10"} - {[Abc Com]} >}[Sale Amt])

[vsap2000]

Thanks, worked for me but it showed top 9 so I have to add 11 instead of 10.

[vsap2000]

Hello,

For the solution given before as shown below I want ,

Sum({<CustName = {"=Rank(Sum(Sale Amt))<=10"}   - {[Abc Com]} ,

                Product Type = {'Apparatus'},    IsYrToDt = {'Y'}

             >}[Sale Amt])

that some one selects from the selector Fiscal Year or Fiscal Period or Fiscal Qtr it should not change so I modified as below,

Sum({<CustName = {"=Rank(Sum(Sale Amt))<=10"}   - {[Abc Com]} ,

                Product Type = {'Apparatus'},    IsYrToDt = {'Y'},

               Fiscal Yr = , Fiscal Period = , Fiscal Qtr=,

             >}[Sale Amt])

it seems because of CustName = {"=Rank(Sum(Sale Amt))<=10"}   - {[Abc Com]} expression 

is not working, so when I select any selectors still the value changes, how I can stop changing the Sale amt value?

Thanks in advance

-vsap2000