Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |