Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jthomson
Solution Sage
Solution Sage

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?

1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
Community Champion

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

2 REPLIES 2
dedelman_clng
Community Champion
Community Champion

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.