Stephan Andersen Posted March 15, 2019 Share Posted March 15, 2019 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. Link to comment Share on other sites More sharing options...
Stefan Hansen 2 Posted March 15, 2019 Share Posted March 15, 2019 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. Link to comment Share on other sites More sharing options...
Richard Pobi Posted March 15, 2019 Share Posted March 15, 2019 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now