Group by Quantity (cumulative total)

[heathernicole]

This is really part One of a problem - I'm trying to create an automated "Scheduler" based on Open Qty. Groups. 

Basically, every 40,000 (Running Total Column) 

Here's my formula for the Scheduling Group: 

Scheduling Group =
IF (
    SUM ( Table_ExternalData_1[Running Total COLUMN] ) <= 40000,
    "GROUP 1",
    IF (
        SUM ( Table_ExternalData_1[Running Total COLUMN] ) > 40000
            && SUM ( Table_ExternalData_1[Running Total COLUMN] ) <= 80000,
        "GROUP 2",
        IF (
            SUM ( Table_ExternalData_1[Running Total COLUMN] ) > 80000
                && SUM ( Table_ExternalData_1[Running Total COLUMN] ) <= 120000,
            "Group 3",
            "GROUP 4"
        )
    )
)

Sample Data:

Event Date      Item #	 SO #	 PO #         Open QTY. RunTot	Index	Scheduling Group
7/5/2017	61303 D	242553	15678718	2100	2100	1	GROUP 1
7/5/2017	61308 D	242553	15678718	600	2700	2	GROUP 1
7/5/2017	61304 D	242553	15678718	2400	5100	3	GROUP 1
7/5/2017	61330 D	242553	15678718	13200	18300	4	GROUP 1
7/5/2017	61322 D	242553	15678718	7800	26100	5	GROUP 1
7/5/2017	61310 D	242553	15678718	600	26700	6	GROUP 1
7/5/2017	61302 D	242553	15678718	300	27000	7	GROUP 1
7/5/2017	61325 D	242553	15678718	4200	31200	8	GROUP 1
7/5/2017	61104 D	242553	15678718	4200	35400	9	GROUP 1
7/5/2017	61311 D	242553	15678718	600	36000	10	GROUP 1
7/5/2017	61324 D	242553	15678718	6600	42600	11	GROUP 2
7/5/2017	61326 D	242553	15678718	600	43200	12	GROUP 2
7/5/2017	61314 D	242553	15678718	1800	45000	13	GROUP 2
7/5/2017	61312 D	242553	15678718	3600	48600	14	GROUP 2
7/5/2017	61320 D	242553	15678718	1800	50400	15	GROUP 2
7/5/2017	61329 D	242553	15678718	8400	58800	16	GROUP 2
7/5/2017	61323 D	242553	15678718	2700	61500	17	GROUP 2
7/5/2017	61315 D	242553	15678718	10200	71700	18	GROUP 2
7/5/2017	61319 D	242553	15678718	4800	76500	19	GROUP 2
7/5/2017	61321 D	242553	15678718	2700	79200	20	GROUP 2

Eventually, what I want to do is schedule 'Group 1' for week ending 1/5/2018. Group 2 for the next week ending date after that, etc. But that part is more of the "Part 2". 

Problem: is there a way to Group by the Running Total Column (by every 40,000) to create the groups rather than manually calculating each group (i.e. >80,000 & <= 120,000, etc...) The quantities get up to almost 2,000,000 total - so that would be a ridiculously long dax measure to determine groups.

Any ideas would be greatly appreciated! @Sean @ImkeF  -do either of you think this kind of a concept is doable?

SIDE NOTE: I needed to create a running total not based off of a date sequence - so the items are ordered by event date, then I created the index field based off of that ordering. From that index field - I created the Running Total (RunTot) column. 

Formula here:

Running Total COLUMN =
CALCULATE (
    SUM ( Table_ExternalData_1[SO Open QTY.] ),
    ALLEXCEPT ( Table_ExternalData_1, Table_ExternalData_1[Index] ),
    Table_ExternalData_1[Index] <= EARLIER ( Table_ExternalData_1[Index] )
)

[Sean · Accepted solution]

@heathernicole

Open the Query Editor

1) Select RunTot column

2) Add Column tab => Standard drop-down =>select Divide (Integer) => enter 40000

this would give you a column that will start from 0

3) select the newly created column => go to Transform tab => again Standard drop down => this time select Add => enter 1

Does this help?

[Sean · Accepted solution]

Here's the DAX Column you can use

DAX Column = "Group " & INT ( DIVIDE ( 'Table'[RunTot], 40000 ) ) + 1

And the result...

[Sean · Accepted solution]

@heathernicole

Open the Query Editor

1) Select RunTot column

2) Add Column tab => Standard drop-down =>select Divide (Integer) => enter 40000

this would give you a column that will start from 0

3) select the newly created column => go to Transform tab => again Standard drop down => this time select Add => enter 1

Does this help?

[Sean · Accepted solution]

Here's the DAX Column you can use

DAX Column = "Group " & INT ( DIVIDE ( 'Table'[RunTot], 40000 ) ) + 1

And the result...

[heathernicole]

@Sean- that worked beautifully!!! THANK YOU!  I'll post part 2 of this problem next. I can't believe how simple that was - and I don't know if I would've thought to do it that way.

Thanks!

[Sean · Accepted solution]

@heathernicole

I posted the Query Editor solution before reading your entire question and realizing that RunTot is a DAX created column which you can't access in the Query Editor. After I saw your formula then I posted the DAX solution which basically does the same thing!

[heathernicole]

Yes - this worked great - I tried to make it a calculated column - it didn't behave the same way. Any theories on why? 

[Sean]

I'm not sure what you mean as the above is a column?

Are you trying to create a Measure instead - in what context will you be using it?

You can't use the RunTot Column in a Measure as it will be aggregated again

You could try using a RunTot Measure instead but I'm unsure as to what you are trying to do

RT Measure =
CALCULATE (
    SUM ( 'Table'[Open QTY.] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) )
)