cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heathernicole
Continued Contributor
Continued Contributor

Group by Quantity (cumulative total)

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] )
)

 

~heathernicoale
3 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

@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?

View solution in original post

Sean
Community Champion
Community Champion

Here's the DAX Column you can use

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

And the result...

DAX Column - INT DIVIDE.png

View solution in original post

Sean
Community Champion
Community Champion

@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! Smiley Happy

View solution in original post

6 REPLIES 6
Sean
Community Champion
Community Champion

@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?

View solution in original post

Sean
Community Champion
Community Champion

Here's the DAX Column you can use

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

And the result...

DAX Column - INT DIVIDE.png

View solution in original post

heathernicole
Continued Contributor
Continued Contributor

@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!

~heathernicoale
Sean
Community Champion
Community Champion

@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! Smiley Happy

View solution in original post

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale
Sean
Community Champion
Community Champion

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] ) )
)

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors