FORMULA for counting rows

[Anonymous]

I've created the table below, which details all of our important Master Opportunities in Salesforce (11 total). Based on this information I want to provide the user with a % of those Opportunities that we've deemed "Power of One" (meaning involves agencies across our network). That total is 10.

Thus, the formula should should exhibiting 90.9% [10/11]. However, I'm not able to configure how to COUNT only the rows of the current visualized table- which again, is 11 (as opposed to the entire # of rows in the ENTIRE TABLE, which is 10K+). As you can see it's returning 100%.

Here's the current formula: 

% Po1 = count(Opportunity[Po1 (to use)]) / CALCULATE(countrows(Opportunity),ALLSELECTED(Opportunity[Name]))

I'm hoping to find how to count only the rows in the table I'm measuring against, NOT the entire table.

[Anonymous · Accepted solution]

@lbendlin 

I was able to resolve this with the following formula:

% Po1 = DIVIDE(CALCULATE(count(Opportunity[Po1 (to use)]),FILTER(Opportunity,Opportunity[Po1 (to use)]="*")),COUNT(Opportunity[Po1 (to use)]))

[lbendlin]

There's a lot of stuff going on. According to your formula "count(Opportunity[Po1 (to use)])" will completely ignore the result of your search and always return the number of rows regardless.  

SEARCH has built-in error handling. You don't need ISERROR.

your #3 looks rather complicated. Describe what the resulting string should look like. Most likely you can write

Po1 ROLLUP = Opportunity[Groupe_BUs_Involved__c] & ", " & Opportunity[Multi PH Agency Opp]

[lbendlin]

please explain your rationale for this part of the formula

count(Opportunity[Po1 (to use)])

Also please show the definition of that field.

[Anonymous]

@lbendlin 

1. count(Opportunity[Po1 (to use)])

Pertains to the first column in the table: it counts the # of Opportunities that have been designated Power of One

2. here's the formula: 

Po1 (to use) = IF(

    ISERROR(

        SEARCH("YES", Opportunity[Po1 ROLLUP])

    ),

    " ",

    "*"

)

3. The formula for "Opportunity[Po1 ROLLUP]" is as follows:

Po1 ROLLUP = CONCATENATE(Opportunity[Groupe_BUs_Involved__c], CONCATENATE(", ",(Opportunity[Multi PH Agency Opp])))