Forum Discussion

jthomson's avatar
jthomson
Solution Sage
7 years ago
Solved

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:

 

 

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:

 

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?

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    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

     

     

    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)

     

     

    Hope this helps

    David

2 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    7 years ago

    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

     

     

    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)

     

     

    Hope this helps

    David

    • jthomson's avatar
      jthomson
      Solution Sage
      7 years ago

      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