Forum Discussion
Grouping Values together in IF statement
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"
)
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 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" )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")Anonymous
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.
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")