Skip to Content
author's profile photo Former Member
Former Member

Top N report customization

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 06, 2009 at 08:58 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 06, 2009 at 01:29 PM

    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)

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 06, 2009 at 01:45 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.