Forum Discussion
Solved
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 ...
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)
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)
