Forum Discussion
Creating dynamic categories using DAX
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 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)
5 Replies
Greg_Deckler intelligent!
Thank you, so much.rashidanwar Thanks! 🙂
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)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 ...... )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)
