Top N report customization

[Former Member]

Hello,

I am working on a report which has 8 key figures. The last key figure is sales and I want to build a report which will display the top 20 customers by sales. Along with this I also want to display the sales to the rest of the customers as the 21st row in my report. This is where I am stuck. I can create a report with top 20 condition but I don't know how to insert the 21st row which will calculate and display the sales to other customers as defined by total sales - sales to top 20 customers.

I will greatly appreciate any help you can provide.

Thanks,

Nikhil

[emjay]

Create a workbook:

1. Query 1 - should display the top 20 customers.

2. Row 21 will have the total sales for the top 20 customers.

3. Query 2 - total sales for all customers. Make this a 1 cell query and put this query in a cell in the workbook and hide the cell.

4. For Row 22 you can say Remaining customers and create formula which will subtract step 3 from step 2.

Hope this helps

[Former Member]

Thanks all, I will give it a try and let you know.

[Former Member]

Could you try the following:

Create another calculated Key figure for sales, say Sales 2, (Sales 1 would be your original sales figure),

go to your key figure properties, select "calculated as Summation" for sales 1, then leave "Nothing defined" for sales 2,

Then in row 21, the total for sales 1 would be the total for top 20 whereas sales 2 would be all the sales,

The next step is to create a formular on row 22 under sales 1 total as "total sales - top 20 total sales" to get your total for the rest of sales .

(Make sure conditions are applied for both sales 1 and sales 2, hide sales 2)

[Former Member]

If you require all the 8 key figures to be displayed from row 21 to further, you can just highlight the sales for Top20 by using exceptions.