Display distribution of a calculated Running Totals

[briguin]

For every minute I want to calculate the total number of cases picked in the last 3 minutes.

I then want to take each calculated minute and see the distribution of those by minute calculations.

I'm searching for bursts of picking speed over 3 minutes and want to display that.

I can calculate the running total in a column and then plot that column.

However, my table is > 800,000 rows and will get larger. It takes a long time to calculate the running total column.

Is there another way to calculate the distributions of the running total vs making a column?

Or have you seen websites, videos or tutorials that do this?

I also know how to calculate the measure in a DAX Measure but can't figure out if I can do a distribution of a calculated measure.

[v-rzhou-msft · Accepted solution]

Hi @briguin 

Create a table to be X axis in visual and create a measure to count.

Table:

X axis = 
VAR _T =
    ADDCOLUMNS (
        Question_Sample,
        "3min_Running_Total",
            CALCULATE (
                SUM ( Question_Sample[Cases_Picked] ),
                FILTER (
                    Question_Sample,
                    Question_Sample[Timestamp] <= EARLIER ( Question_Sample[Timestamp] )
                        && Question_Sample[Timestamp]
                            > EARLIER ( Question_Sample[Timestamp] ) - TIME ( 0, 3, 0 )
                        && Question_Sample[Pick_Area] = EARLIER ( Question_Sample[Pick_Area] )
                        && NOT ( ISBLANK ( Question_Sample[Timestamp] ) )
                )
            )
    )
VAR _Xaxis =
    GENERATESERIES (
        MINX ( _T, [3min_Running_Total] ),
        MAXX ( _T, [3min_Running_Total] )
    )
RETURN
    _Xaxis

Measure:

Count = 
VAR _T =
    ADDCOLUMNS (
        Question_Sample,
        "3min_Running_Total",
            CALCULATE (
                SUM ( Question_Sample[Cases_Picked] ),
                FILTER (
                    Question_Sample,
                    Question_Sample[Timestamp] <= EARLIER ( Question_Sample[Timestamp] )
                        && Question_Sample[Timestamp]
                            > EARLIER ( Question_Sample[Timestamp] ) - TIME ( 0, 3, 0 )
                        && Question_Sample[Pick_Area] = EARLIER ( Question_Sample[Pick_Area] )
                        && NOT ( ISBLANK ( Question_Sample[Timestamp] ) )
                )
            )
    )
RETURN
    COUNTAX (
        FILTER ( _T, [3min_Running_Total] = SUM ( 'X axis'[Value] ) ),
        [3min_Running_Total]
    )

Result is the as you create a calculated column.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

To do it as a measure (not sure it will be faster but worth trying), you can make a disconnected table with all the potential values for your x axis (e.g., with GENERATESERIES), and then write a measure that creates a virtual summary table of 3 minute speeds and count the rows of that table FILTERed to the SELECTEDVALUE of your disconnected table.

You could also consider adding a column with time rounded to 3-minute intervals to further simplify things.  But if you need a 3 minute total for every minute, that won't work.

Pat