Home Forums Power Pivot TopN and All other

Tagged: ,

This topic contains 2 replies, has 2 voices, and was last updated by  mikechina 3 years, 8 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #1982

    mikechina
    Participant
    • Started: 12
    • Replies: 38
    • Total: 50

    To all smart people on this forum,

    Has anyone found a good way of displaying top N and all other as a dimension?  I can put a rank as a calculated column and do it that way, but I was wondering if there was another way.  I was looking at Rob’s post here http://www.powerpivotpro.com/2014/11/displaying-top-n-bottom-n-and-all-others/, but it groups the top and bottom.  I looking for a way to leave the top ungrouped.  Any ideas appreciated.

    Thanks,

    Mike

    #1986

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Hi Mike,

    Focusing on leaving the top N ungrouped and the rest grouped, let me toss out an idea and you can tell me how far I have missed the target.

    Instead of one calculated column, you could use two: The first calculated column would be to rank all of the rows (I will name this column “Sort”), the second calculated column would be a text column that follows logic like this (assuming for this case you are separating the top 10):

    Group = IF ( Table1[Sort] <= 10, FORMAT ( Table1[Sort], “00” ), “Others” )

    Or you could combine the two calculated columns into one.

    Please see attached workbook.

    Let me know what you think.

    Attachments:
    You must be logged in to view attached files.
    #1992

    mikechina
    Participant
    • Started: 12
    • Replies: 38
    • Total: 50

    Thanks Tom!  This is a real good solution.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.