Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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] ) )
Solved! Go to Solution.
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?
Here's the DAX Column you can use
DAX Column = "Group " & INT ( DIVIDE ( 'Table'[RunTot], 40000 ) ) + 1
And the result...
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!
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?
Here's the DAX Column you can use
DAX Column = "Group " & INT ( DIVIDE ( 'Table'[RunTot], 40000 ) ) + 1
And the result...
@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!
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!
Yes - this worked great - I tried to make it a calculated column - it didn't behave the same way. Any theories on why?
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] ) ) )
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |