Select TopN of one column, then sum the values corresponding in another column

[aslee]

Hi,

What I would like is to write a calculated measure that sums the Points, for the N highest Iterations. 

 Iteration           Points

113                   1

112                   49

111                   10

110                   52

109                   44

This data will be updated regularly, and I only want to see the summation of the most recent iterations, without having to select the iterations in a slicer (for example, N=3, I would only want to see the summation of the Points of Iteration 113, 112, 111 = 1 + 49 + 10 = 60, and next week it would automatically sum the points for Iterations 114, 113, 112)

When I try to use TopN, it's not giving me what the right number. 

SumTop3 = CALCULATE(SUM(velocity[points]), TOPN(3, velocity, velocity[iteration]))

Any help would be appreciated.

Thanks in advance,

Annie

[Vvelarde · Accepted solution]

@aslee

Hi, if you iterations are number you can do it this:

Top3Iterations-Points =
    VAR Maxiteration =
        MAX ( 'Iterations-Table'[Iteration] )
    RETURN
        CALCULATE (
            SUM ( 'Iterations-Table'[Points] ),
            FILTER ( 'Iterations-Table', 'Iterations-Table'[Iteration] >= Maxiteration - 2 )
        )

[BhaveshPatel]

As far as I understand your Question,

You should create a measure

sum of points:=SUM(velocity[points])

and in visual level filters, you can select, TOPN or BOTTOMN values in the latest october release. see the attached screenshot.

Visual level filters

Hoping that this is what you are expecting.

[aslee]

Hi Bhavesh,

By most recent iterations, I mean the highest number in terms of the number assigned to the iteration. This week we are in iteration 113, next week we will be in iteration 114, so as time goes on, the iteration increases. 

So what I want is an up to date summation of points from today and the previous 2 iterations. Basically I want the measure to select the highest 3 numbers in the iteration column (eg. 113, 112, 111 at this point in time) and then sum the points associated with those iterations only. 

The measure you suggested will calculate a sum based on the highest scoring points, ie 52 + 49 + 44. 

[Vvelarde · Accepted solution]

@aslee

Hi, if you iterations are number you can do it this:

Top3Iterations-Points =
    VAR Maxiteration =
        MAX ( 'Iterations-Table'[Iteration] )
    RETURN
        CALCULATE (
            SUM ( 'Iterations-Table'[Points] ),
            FILTER ( 'Iterations-Table', 'Iterations-Table'[Iteration] >= Maxiteration - 2 )
        )

[aslee]

@Vvelarde @Sean Wondering if either of you could help me futher

When using the Card visualisation, I get the number I want. 

But when I go to make this a column chart, with [Iteration] as the x-axis, I get a column chart with a bar for every iteration, not just the top 3. I would have thought using the calculated measure as my Value would limit the Iterations to only 113, 112, 111? 

Any ideas? Thanks in advance! 

[Sean]

Go to the Visual Level Filters => open the Iteration field => Filter Type: Top N => Show Items: Top type 3

By Value drag the Iteration field from the Fields of your table => click the down arrow => select Maximum =>Apply Filter

[aslee]

Amazing, thank you @Sean!

[aslee]

Thank you @Vvelarde , that worked brilliantly! I am relatively new to DAX, thanks again 🙂 

[Sean · Accepted solution]

This will also address the multiple rows of the same last 3 iterations

Basically Filter the velocity table to only the rows where [Iteration] is greater than or equal to the Max (114-2)

which would be all rows with 114, 113 and 112

Hope this helps!

SumTop3 ALT =
    CALCULATE (
        SUM ( velocity[Points] ),
        FILTER (
            velocity ,
            velocity[Iteration]
                >= MAX ( velocity[Iteration] ) - 2
        )
    )

EDIT: Yep actually don't see the need for a Variable here...

[aslee]

@Sean sorry I don't quite understand what you mean by multiplw rows of the same last 3 iterations?? 

[Sean]

If the last 3 iterations are 113, 112, and 111

In your original dataset - you have multiple rows of these 3 values - for example say 3 rows of 112 and 2 rows of 111

meaning the [Iteration] column can contain duplicates which is what trips up the TOPN function

113 - 1

112 - 20

112 - 20

112 - 9

111 - 5

111 - 4

[aslee]

@Sean Oh I see! Generally we shouldn't have more multiple rows of the same iteration number, but that is a good tip for future reference! Thanks for your help 🙂 

[Sean]

@aslee Yes you must have multiple rows of the last 3 iterations...

@Vvelarde's solution will take care of this!

[Sean]

@aslee What answer do you get?

[aslee]

Hi Sean,

I get 50 using that measure. 

My full set of data is the below - I only included the top few in my first post for illustrative purposes. 

 Iteration           Points

113                   1

112                   49

111                   10

110                   52

109                   44

108                   54

107                   44

106                   16

105                   3

[BhaveshPatel]

Hi Annie,

How do you define most recent iterations. Do you mean first 3 iterations by Date or Points?