cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LyonsBI_BRL
Helper III
Helper III

Grouping Values together in IF statement

I'm completely stuck on an IF statement that I need to create here for the following scenario

The scenariio is I need to create a calculated column (that or if a measure would work better that'd work too) Where IF the # of Units is greater than 1000 = DEALS for that Company, Under 500 Units, it would be "No Deals" for that company.

 

Omitting customer data below, It'd be if say Contoso had more than 1000 total units across the different Areas and Sub Regions it would equal a DEAL. If the overall total was under 500 it'd be No Deal. 

Somewhere we could have Between 500 and 1000 it'd be "Good Deal"

Anyone have any thoughts on how I could assemble this formula here as I'm really drawing a blank here. 

 

LyonsBI_BRL_0-1620958593549.png

 

Thanks!

 

9 REPLIES 9
v-yangliu-msft
Community Support
Community Support

Hi  @LyonsBI_BRL   ,

Here are the steps you can follow:

1. Create measure.

Flag =
var _table=SUMMARIZE(ALL('Query1'),[Segement Group],[of Units],"1",[MegaDeals_New])
var _max=MAXX(FILTER(_table,[1]=[MegaDeals_New]),[of Units])
return
IF(MAX([of Units])=_max,1,0)

2. Place the Flag in the Filter, set is =1, Apply filter.

v-yangliu-msft_0-1621590251629.png

3. Result.

Only one in the same group

v-yangliu-msft_1-1621590251633.png

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yangliu-msft 

So I put together the 

Flag = 
var _table=SUMMARIZE(ALL('Query1'),[Segment Group],[[Units]]],"1",[MegaDeals_New])
var _max=MAXX(FILTER(_table,[1]=[MegaDeals_New]),[[Units]]])
return
IF(MAX([[ST Units]]])=_max,1,0)

Though it wasn't seeming like it was grouping together when I placed the Flag Measure into the table. Instead it just took a huge amount of time and swtiched from being Flag = 1 to showing as 0 in the data card. 

Am I missing something here?

LyonsBI_BRL_2-1622105174219.png

 

 

 

 

@v-yangliu-msft 

I tried the solution you posted last Friday

Flag =
var _table=SUMMARIZE(ALL('Query1'),[Segement Group],[of Units],"1",[MegaDeals_New])
var _max=MAXX(FILTER(_table,[1]=[MegaDeals_New]),[of Units])
return
IF(MAX([of Units])=_max,1,0)

However when I placed it into the table, it actually cleared everything and didn't display anything. Does it Matter if it's Summarize of Segment Group or can it also be Summarize of Business Type or Organization (the name of the Company)

 

Thanks!

v-yangliu-msft
Community Support
Community Support

Hi  @LyonsBI_BRL  ,

Here are the steps you can follow:

1. Create measure.

MegaDeals_New =
Var _Company=MAX(Query1[Organizationt])
Var _TotalUnits=SUMX(FILTER(ALL(Query1),Query1[Organization]=_Company),Query1[[Units])
Return
SWITCH(True(),
_TotalUnits > 1000, "Mega Deal",
_TotalUnits <500, "Under 500",
"Not Mega")
card =
COUNTAX(FILTER('Query1','Query1'[MegaDeals_New]="Mega Deal"),'Query1'[Organization])

2. Place [Organization] in the slicer, and place [card] in the card image

3. The result looks like this

v-yangliu-msft_0-1621387953893.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

@LyonsBI_BRL Not certain I understand 100% but try this measure along with Company in a table visual:

 

Measure = 
  VAR __TotalUnits = SUM('Table'[# of Units])
RETURN
  SWITCH(TRUE(),
    __TotalUnits > 1000, "DEAL",
    __TotalUnits < 500, "No Deal",
    "Good Deal"
  )

 

 

 


@ 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

@Greg_Deckler 

Hey Greg thank you for getting back to me!

The formula above worked pretty well!

 

Speaking with the customer, what they want is if you look below at the graphic, The total number of units equals 2545. What they would like to see is if the total number is greater than 1000 than it equals a Mega Deal, if it's under 500 its "Under 500" and finally if its between the two its "Not Mega". So basically the 2545 would be grouped into one row instead multiple rows, making it a mega deal. 

 

Is there a way to group all of them together within the formula? If so any additional help would be greatly appreciated. 

 

Thanks!

 

MegaDeals = VAR _TotalUnits =SUM(Query1[[Units]]])
Return
SWITCH(TRUE(),
    _TotalUnits > 1000, "Mega Deals",
    _TotalUnits < 500, "Under 500",
"Not Mega"
)

 

LyonsBI_BRL_0-1621016778848.png

 

@LyonsBI_BRL Sure, 

Measure = 
  VAR __Company = MAX('Table'[Company])
  VAR __TotalUnits = SUMX(FILTER(ALL('Table'),[Company]=__Company),[# of Units])
RETURN
  SWITCH(TRUE(),
    __TotalUnits > 1000, "DEAL",
    __TotalUnits < 500, "No Deal",
    "Good Deal"
  )

@ 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

@Greg_Deckler 

Hey Greg! That worked like a charm WOW!

So we ended up creating the measure as you have described above. 

 

We are overlooking one last hurdle, Will this count the number of Mega Deals then by company? So the screen shot below (Company name omitted), Will it count Mega Deals as 1 deal for that company? If so, how can I display that as I attempted to create a CARD though I have no option to select COUNT this as 1 deal. Any suggestions here? 

 

Thank you again as this has been extremely helpful!

MegaDeals_New =
Var _Company=MAX(Query1[Organizationt]])
Var _TotalUnits=SUMX(FILTER(ALL(Query1),Query1[Organization]]=_Company),Query1[[Units]]])
Return
SWITCH(True(),
_TotalUnits > 1000, "Mega Deal",
_TotalUnits <500, "Under 500",
"Not Mega")

LyonsBI_BRL_0-1621274694021.png

 

@v-yangliu-msft 

What we need here is we need to group everything together so instead of it says we have 4 Mega Deals, we want it grouped into ONE row.

LyonsBI_BRL_0-1621544147690.png

This is still the current code I'm running in the measure. I'm missing a key component here, how would I include GROUPBY. I'd like to GROUPBY Organization

This is super close, just instead of it thinking I have 4 rows of MegaDeals totalling 3500, I want it to be ONE row, that's been grouped together equaling a MegaDeal

MegaDeals_New =
Var _Company=MAX(Query1[Organizationt]])
Var _TotalUnits=SUMX(FILTER(ALL(Query1),Query1[Organization]]=_Company),Query1[[Units]]])
Return
SWITCH(True(),
_TotalUnits > 1000, "Mega Deal",
_TotalUnits <500, "Under 500",
"Not Mega")

 

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.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

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!