Reply
Super User
Posts: 447
Registered: ‎07-12-2017
Accepted Solution

Cumulative sum issues

Hi,

 

Data is as below:

 

QB NamePicks Thrown
Tom Brady0
Tom Brady0
Tom Brady1
Tom Brady2
Tom Brady4
Tom Brady3
Tom Brady1
Tom Brady3
Tom Brady2
Tom Brady1
Tom Brady0
Tom Brady0
Tom Brady2
Tom Brady1
Tom Brady0
Nathan Peterman4
Nathan Peterman3
Nathan Peterman4
Nathan Peterman0
Nathan Peterman1
Nathan Peterman1
Nathan Peterman4
Nathan Peterman3
Nathan Peterman3
Nathan Peterman4
Nathan Peterman3
Nathan Peterman4
Nathan Peterman1
Nathan Peterman1
Nathan Peterman0

 

What I'm wanting to do is plot a graph that can show me the number of games where a QB has thrown at most 0, 1, 2 etc interceptions. So I count the number of games with a simple measure Games Played = countrows(Table1), and then I'm trying the following measure:

 

Games played with n or less picks = calculate([Games Played], filter(allselected(Table1),Table1[Picks Thrown]<=max(Table1[Picks Thrown])))

 

This works perfectly:

 

cumulative sum 1.PNG

 

Now if I want to filter just Peterman's games, him not having a two-interception game is giving unwanted results. If I select his name from the other visual, this happens:

 

cumulative sum 2.png

and if I hover over the value for two interceptions, it's calculating the measure to give 2. If I add a slicer instead, rather than evaluating the measure "incorrectly", it doesn't evaluate the measure at all and just completes a line directly from x-axis values 1 and 3 to y-axis values 6 and 10. Does anyone have any ideas how I can reword the cumulative measure so that it evaluates Peterman as having six games with two or less interceptions?


Accepted Solutions
New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Cumulative sum issues

HI @jthomson

 

I was able to get it to work by doing the following:

 

Create a new table for all of the values in "Picks Thrown"

IntNums = VALUES(Ints[Picks Thrown])

Join to the Interceptions table

 

int model.png

 

Modified the measure to filter on IntNums table

 

Games played with n or less picks =
CALCULATE (
    [Games Played],
    FILTER (
        ALLSELECTED ( IntNums ),
        IntNums[Picks Thrown] <= MAX ( IntNums[Picks Thrown] )
    )
)

Use IntNums as the axis for the bar/line chart (and for the rows on the tables)

 

int1.pngint2.png

 

Hope this helps

David

View solution in original post


All Replies
New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Cumulative sum issues

HI @jthomson

 

I was able to get it to work by doing the following:

 

Create a new table for all of the values in "Picks Thrown"

IntNums = VALUES(Ints[Picks Thrown])

Join to the Interceptions table

 

int model.png

 

Modified the measure to filter on IntNums table

 

Games played with n or less picks =
CALCULATE (
    [Games Played],
    FILTER (
        ALLSELECTED ( IntNums ),
        IntNums[Picks Thrown] <= MAX ( IntNums[Picks Thrown] )
    )
)

Use IntNums as the axis for the bar/line chart (and for the rows on the tables)

 

int1.pngint2.png

 

Hope this helps

David

Highlighted
Super User
Posts: 447
Registered: ‎07-12-2017

Re: Cumulative sum issues

Cheers, tweaked it a bit to use generateseries between 0 and the max value of the data, but the concept of using a separate table as the axis is working fine