Forum Discussion

tracyhopaulson's avatar
tracyhopaulson
Resolver I
5 years ago
Solved

How to dynamically group values

I've grouped a list below but each time the report is refreshed, the new data get thrown into the "other" bucket and I have to manually group them.  Is there another option to setup dynamic grouping ...
Need Help
  • negi007's avatar
    negi007
    5 years ago

    tracyhopaulson You can do one thing, replace blank values in your data with a -1. You can make changes in the powerquery window like below and then change your dax as well. Performing calculation with blank values will not be possible, it is advisible to have numeric values in the data

     

     

    Grouping =
    Var day_chg = IF(ISBLANK('Order'[#daysChange]),-1,'Order'[#daysChange])
    return
    if(day_chg = -1,"Forecast",
    if(day_chg=0,"On time",
    if(AND(day_chg>20,day_chg<40), "Late",
    if(AND(day_chg>40, day_chg<100), "Very Late","Others"
    ))))

     


     
  • tracyhopaulson's avatar
    tracyhopaulson
    5 years ago

    negi007  thanks again for your help! Yes this is exactly what I need. 

     

     

tracyhopaulson's avatar
tracyhopaulson
Resolver I
5 years ago

negi007  creating custom column with the formula you provided works.  Thanks so much!  However, I now noticed there are some orders with missing Promised Date and Scheduled Date so the #ofDaysChg is blank.  Somehow it groups these into the On Time bucket.  I would like to group them into different so I added condition if(day_chg=blank(),"Forecast", but it doesn't work.  What am I missing?

negi007's avatar
negi007
Community Champion
5 years ago

tracyhopaulson You can do one thing, replace blank values in your data with a -1. You can make changes in the powerquery window like below and then change your dax as well. Performing calculation with blank values will not be possible, it is advisible to have numeric values in the data

 

 

Grouping =
Var day_chg = IF(ISBLANK('Order'[#daysChange]),-1,'Order'[#daysChange])
return
if(day_chg = -1,"Forecast",
if(day_chg=0,"On time",
if(AND(day_chg>20,day_chg<40), "Late",
if(AND(day_chg>40, day_chg<100), "Very Late","Others"
))))

 


 
  • tracyhopaulson's avatar
    tracyhopaulson
    Resolver I
    5 years ago

    negi007  thanks again for your help! Yes this is exactly what I need.