Case When with And statement to look at two values to determine value given

[Stephan Andersen]

I have a situation where i have the following

Multiple Business Names

Sales Opportunities that have an Opportunity number, but within that Opportunity number there are multiple rows that make up the sum total of that opportunity.

What I want to do is say if the business name = "X" and the total opportunity value is greater than a certain $ value, then call it a "whale".

I have multiple businesses within the same set of data and each business would have it's own threshold for what would be considered a whale, but the key is that I have to add all of the rows related to that opportunity, I can't just use one of the rows.

I feel like this is a case when statement, but struggling to do this where I can take both the name and the total dollar value of the opportunity to come up with the name.

[Stefan Hansen 2]

Hi Stephan

Sounds like you could create a new table where you pivot the data so that all Business names are on a single row, you can have both the tables in the analysis and use the table you are interested in, but it depends on how you do the calculations.

[Richard Pobi]

You need to create two calculated columns.

1st Sum_Sales_Number

Sum(Sales Opportunities) OVER (business name Column)

2cd Calculated column

Case

when Sum_Sales_Number >= 100 THEN X

when (Sum_Sales_Number=50) THEN Y

when Sum_Sales_Number > 100 THEN Z

END