Try creating the calculated field below, then show the sum of that field.
IF [Status] = "Open" THEN 1 ELSE 0 END
This worked! Thanks so much.
Another quick add-on to that: Lets say I wanted to add multiple criteria (In this case Open + Won), how would I write that calculated field?
Glad to help!
The following should work:
IF ([Status] = "Open") OR ([Status] = "Won") THEN 1 ELSE 0 END
If you want to continue with a calculated field then you could do something like IF [Status] = 'Open' or [Status] = 'Won' then 1 else 0
If these are just dimension values, you might be overthinking it and you could potentially use 'Number of Records' along with the dimension itself. I.e. lets say I have a dimension called region - drop that on the rows and the number of records on the columns;
The reason for the calculated field is because I'm going to be displaying it on a matrix with other calculated fields. Thanks though!
This worked! Thanks again.
Hey Stephen I actually have one more related question for you if you don't mind.
I have a "Lead" column as well, where the value is numeric (Either a 1 or a 0). I want to sum the total Leads where Status = "Open". I tried using the above formula, but it gives me an error "Cannot compare integer and string values". How would I go about doing this without changing my data type?
Are you sure you want that in a calculated field? You could get the total number of leads for open statuses by added [Status] to your rows and SUM([Lead]) in the values area...
With that said, you could get a calculated field with the result by creating the following calculated field
IF Status = "Open" THEN [Lead] ELSE 0 END
Then looking at the sum.
Understood, the reason for the calculated fields is because I'm building a matrix with a bunch of different metrics so I can't use Status as a row. I actually figured it out just as you sent the reply, wrote:
IF [Lead]=1 AND [Status (Lead)] = "Open" THEN 1 ELSE 0 END
This seemed to work. Thanks again for your help!