Results 1 to 3 of 3

Thread: TOPN function

  1. #1

    TOPN function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    What is correct syntax to SUM all items sales that comprise top 5(n)? I've tried numerous examples SUMX, Summarize, etc. Still no luck.



    Thanks,

    Spence

  2. #2
    Seeker Owen Auger's Avatar
    Join Date
    Apr 2016
    Posts
    14
    Articles
    0
    Excel Version
    2016
    Hi Spence,

    In case you're still looking for a solution, here a few variations of a TopN-type measure using the TOPN function to define the filter, depending on the behaviour you want. These don't cover every possibility but should get you started.

    I've assumed that you have a [Sales] measure defined and your Item column is Sales[Item].

    Code:
    Sales Top 5 Items In Current Context =
    CALCULATE ( [Sales], TOPN ( 5, VALUES ( Sales[Item] ), [Sales] ) )
    
    
    Sales Top 5 Items Regardless of Item Filter =
    CALCULATE ( [Sales], TOPN ( 5, ALL ( Sales[Item] ), [Sales] ) )
    
    
    Sales Top 5 Items Intersecting with Current Context =
    CALCULATE (
        [Sales],
        TOPN ( 5, ALL ( Sales[Item] ), [Sales] ),
        VALUES ( Sales[Item] )
    )
    Owen Auger, CFA

  3. #3
    Owen... Worked perfectly. Exactly what I needed. Also appreciate the other variations. These will come in SOOOO handy.

    Best,
    Spencer

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •