DAX TOPN function

[Dicken]

Hi,
Is there a way to get a distinct list of Topn nth values, or a list of all of the values that are in eg top 3,

so in the list; 1, 2,2,2,3,3,4,5,6,7,7,7 8,8,9,10,10

Top 3 ; 10,9, 8, and all occurrence of top ; 3 8,8,9,10,10

I've been trying to sum the total for the top 3 values but am not getting the answer I want; I can see what it's doing by
putting it into DAX studio, If I put

Please Login or Register  to view this content.

I get back a table of 5 rows of value totoal 75,, so i see what it's doing, returning position 1 and 2 and the ties for 3,
if I change to six i get the ties for six.

The formula I have is

Please Login or Register  to view this content.

I've attached a workbook with excel example of what I'd like to get back and the incorrect and correctish result, it's correct for the grand total.

RD

[romperstomper]

I think you mean something like this?

Please Login or Register  to view this content.

[HansDouwe]

To retrieve result 142 please try

Please Login or Register  to view this content.

but I don't understand the calculations of TOPN wanted pro item.
Why is that for item A 121?

[Dicken]

Re Item A giving 121, I don't know either I've been trying to see what its calculating to get that, if I fgure it out I'll let you know.


rd

[Dicken]

That, certainly give the correct, total, but I stll figure out what it's doing drop the item into a pivot table ,
81, 49 , 50 42 , with even with my math's don't make 142, still thanks. One thing, when I tried your formula in DAX studio it returned an error, but worked fine in 'reality', curious, which just about sums up the function.

rd

NB the reason the dax studio didn't work is I forgot to add the { } to scaler, suddenly realized making tea.

[romperstomper]

Measures do the same calculation at every level. 142 is not the sum of the other numbers. It's the sum of all values matching the top 3 values overall.

[HansDouwe]

I'm glad t o have helped and thanks for the feedback and rep .

[romperstomper]

Re Item A giving 121, I don't know either I've been trying to see what its calculating to get that, if I fgure it out I'll let you know.


rd

It's the top 10 rows for A. If there is a tie at the last item (as here), it includes all rows matching that item, so you get both 7 values.