locked
Cumulative Totals via DAX RRS feed

  • Question

  • I have a question about a cumulative or rolling sum using DAX.  I have sequenced a bunch of records within SQL and loaded them into my model.  Users will do different analysis around the sequence number of records i.e. what percent of records were reached after sequence number 5.  I currently have 3 measures, two of which are calculated (totalCompletes and Pct Total Completes).

    The code for the measures in the PowerView below is:

    SumCompletes:=SUM([Completes])
    
    TotalCompletes:=CALCULATE([SumCompletes],ALL('Dials Sequenced'))
    
    Pct Total Completes:=[SumCompletes]/[TotalCompletes]


    By looking at the data as it is now, one can infer that ~55% of the total was reached after sequence 2.  I would like to create a rolling pct sum that users could place on the report to automatically roll the pct total up so they don't have to do it in their heads.  This would display sequence 1 as 36%, sequence 2 would be 55%, 3 would be 66% and so on.

    Most of the examples I've seen use dates to accomplish this but I managed to put a measure together with one problem.  While it works well with smaller datasets, it times out with larger ones.  The code for it is below:

    Cumulative CC Pct:=CALCULATE([SumCompletes],
    	FILTER(ALL('Dials Sequenced'),'Dials Sequenced'[DialCampSequence] <= MAX('Dials Sequenced'[DialCampSequence])
    	)
    )/[TotalCompletes]
    The problem that I'm having with this is query timeout.  In a tabular model I can get this to work on several days worth of data (~10 million rows), but when i get to 1 years worth (~200 million) the query just spins until timeout.  This is a very popular way of looking at data but if we can't get the query to run in a reasonable amount of time then it's useless.  Is there something I'm doing wrong here?


    Friday, December 12, 2014 3:41 PM

Answers

  • So after messing around with this a bit more I seem to have figured out a fix.  I took a look at the data and found that there were several numbers that had sequences going up to 95k!  Without knowing too much about the DAX formula engine I imagine that this amount of recursion across hundreds of millions of rows 95k times is just too much.

    In the future I think best practice would be to remove these numbers as they were junk anyway but for now I was able to get queries to run by first filtering down the number of sequences.  In effect I imagine this limits the number of recursion by reducing the size of the initial table.

    After first limiting the number of sequences and then dropping on the Cumulative CC Pct measure the query returned in just a few seconds with the expected results.

    Friday, December 12, 2014 5:26 PM