Creating dynamic categories using DAX

[rashidanwar]

Hi everyone,

I have a problem that is more mathetical infact. I would appreciate if someone helps.

I have a table as shown below 

orderline_id             order_id            qty
1                              1                        3
2                              1                        2
3                              1                        1
1                              2                        6
2                              2                        5
1                              3                        9
2                              3                        11

I need to create a 4th Column that will be the qunatity group column based on the SUM of qty for each order_id. Each group will be based on the following logic.
SUM of qty Column 1-9, then category would be 1 
SUM of qty Column 10-18, then category would be 2
SUM of qty Column 19-27, then category would be 3
and so on 

Forexample the above table would like as below.
orderline_id             order_id            qty          qty_group
1                              1                        3              1
2                              1                        2              1
3                              1                        1              1
1                              2                        6              2
2                              2                        5              2
1                              3                        9              3
2                              3                        11            3

In case of order_id 1 the sum of Qty would be 6 so it lies in the range of 1-9 so the Qty_group would be 1
In case of order_id 2 the sum of Qty would be 11 so it lies in the range of 10-18 so the Qty_group would be 2
In case of order_id 3 the sum of Qty would be 20 so it lies in the range of 19-27 so the Qty_group would be 3

Actually we need to calculate the qty Group dynamically, bcause the Sum of Qty for an individual order id can go as high as 1600.

Looking fo

[rashidanwar]

@Greg_Deckler intelligent!
Thank you, so much. 

[Greg_Deckler]

@rashidanwar Thanks! 🙂

[Greg_Deckler]

@rashidanwar Try a measure like this:

Measure =
  VAR __qty = SUM('Table'[qty])
RETURN
  SWITCH(TRUE(),
    __qty < 10,1
    __qty < 19,2
    3
  )

or try this:

Measure = 
    VAR __qty = SUM('Table'[Column1])
    VAR __Mod = MOD(__qty,9)
RETURN
    IF(__Mod = 0, DIVIDE(__qty,9), TRUNC(DIVIDE(__qty,9))+1)

[rashidanwar]

Thank you @Greg_Deckler.
You are right and I am doing already this. Problem is that there are alot of orders and sum of qunatity can go up to 1600 for a single order, that is why a static measure would not be a good option. There should be some way to increment the logic. 
for example value paramter of switch function is icremented by 9 and the result parameter is incremented by 1.

Some kind of looping is refquired here.

If I go using static logic then you see that I just extended your solution to 10 categories and we still reach at the value of 91, and 1600 is too far away.

SWITCH(TRUE(),
    __qty < 10,1
    __qty < 19,2
    __qty < 28,3
    __qty < 37,4
    __qty < 46,5
    __qty < 55,6
    __qty < 64,7
    __qty < 73,8
    __qty < 82,9
    __qty < 91,10
    ......
  )

[Greg_Deckler · Accepted solution]

@rashidanwar Try the alternate formula I posted:

Measure = 
    VAR __qty = SUM('Table'[Column1])
    VAR __Mod = MOD(__qty,9)
RETURN
    IF(__Mod = 0, DIVIDE(__qty,9), TRUNC(DIVIDE(__qty,9))+1)