TOPN function

Spencer_2

New member
Joined
Nov 29, 2016
Messages
13
Reaction score
0
Points
0
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
 

Owen Auger

New member
Joined
Apr 26, 2016
Messages
14
Reaction score
0
Points
0
Excel Version(s)
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] )
)
 

Spencer_2

New member
Joined
Nov 29, 2016
Messages
13
Reaction score
0
Points
0
Owen... Worked perfectly. Exactly what I needed. Also appreciate the other variations. These will come in SOOOO handy.

Best,
Spencer
 
Top