cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tracyhopaulson
Resolver I
Resolver I

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 in PowerBI?  Thanks in advance for your help!

 

Capture1.PNGCapture1.PNG

2 ACCEPTED SOLUTIONS

@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

 

replace value.png

 

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"
))))

negi007_0-1602207495170.png

 


 



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


View solution in original post

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

 

 

thank-you.gif

View solution in original post

5 REPLIES 5
negi007
Super User
Super User

@tracyhopaulson you can create a custom column like below. You can add more if conditions as per your requirements.

 

Grouping =
Var day_chg = 'Order'[#daysChange]
return
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"
)))


 

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


@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?

@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

 

replace value.png

 

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"
))))

negi007_0-1602207495170.png

 


 



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


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

 

 

thank-you.gif

Greg_Deckler
Super User
Super User

@tracyhopaulson Not sure about the issue you are having with the grouping/binning, but you could always just create a measure, like:

Measure = 
  VAR __DateGrouping = MAX([DateGrouping])
RETURN
  SWITCH(TRUE(),
    __DateGrouping = "Late","1-99 days",
    __DateGrouping = "On Time","0 days",
    __DateGrouping = "Very Late","100+ days",
    __DateGrouping = "Very Early","Future",
    "Other"
  )
    
    

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!