Forum Discussion
Continued ContributorGroup by Quantity (cumulative total)
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! :smileyhappy:
Community ChampionHere's the DAX Column you can use
DAX Column = "Group " & INT ( DIVIDE ( 'Table'[RunTot], 40000 ) ) + 1
And the result...
Continued ContributorSean- 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
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! :smileyhappy:
- 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] ) ) )