Help with creating a FORMULA for counting rows of a table.

[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.

Any help is appreciated.

[Anonymous]

I believe the formula should be something like this:

% Po1 = count(Opportunity[Po1 (to use)]) / count(Opportunity[Include_in_Commercial_Pulse__c]

But I receive the following error message: 

[AlB]

@Anonymous 

Ok, but what is the restriction for "Po1 (to use)" to get the 10 values?? cause if you just do

COUNT(Opportunity[Po1 (to use)])

it seems like you should get the 11, right? i.e. the 11 rows that are active in the filter context stemming from the two filters you described

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB ,

Thank you for the quick reply.

The 11 are from 2  denoting:

• "Include_in_Commercial_Pulse__c" = TRUE
• "StageName" = OPEN

The 10 come from a New Column I created from the following formula:

Po1 (to use) = IF(

    ISERROR(

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

    ),

    " ",

    "*"

)

Basically, the table is used to illustrate our Top Opportunities, and the Po1 column denotes which ones involved multiple agencies. I want to then show the % of the Top Opps that are Po1.

[AlB]

Hi @Anonymous 

I see 11 rows in the table visual you are showing. Why is that? Where is the 10 coming from? And the 11 (i.e., the currently visualized table)? What filters are active on the table that will determine those 10 rows for the denominator in your formula  and, alos, what filters will determine the 11?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB:

• The 11 come from the filter: Include_in_Commercial_Pulse__c = TRUE
  • Our Salesforce instance has 10K+ opportunities. But only a handful are deemed worthy of this bi-weekly report. Thus, we have a checkbox on each Opportunity, denoting "Include in Commercial Pulse." If the box is checked, then it's automatically synced to PowerBI.
• The 10 come from the following formula: 

Po1 (to use) = IF(

    ISERROR(

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

    ),

    " ",

    "*"

)

[Anonymous]

@AlB any thoughts to what I provided?