Report formula - percentage based on number of rows returned by report

[wkuehler]

I have a simple summary report (with details hidden) that looks like this

Overall Rating Number: 1 (9 records)
Overall Rating Number: 2 (7 records)
Overall Rating Number: 3 (6 records)
Overall Rating Number: 4 (13 records)
         Grand Totals (35 records)

Is there a way to display percentages for each line to display the following (or something similar):

Overall Rating Number: 1 (9 records)               26%
Overall Rating Number: 2 (7 records)               20%
Overall Rating Number: 3 (6 records)               17%
Overall Rating Number: 4 (13 records)             37%
         Grand Totals (35 records)

I have not been able to create a summary formula that can do this.   it would look something like this:

Number_of_rows_summarized / Total_rows_returned_by_report

Any ideas?

wkuehler

[chris925]

Hi wkuehler,

I think this is what you're asking for - can you create a custom formula on that report, with a format like this:

{!Lead.Lead_Rating__cUM} / {!RowCount}

and then group by Lead Rating?  You could get really fancy and make it a matrix report and then drill across by some other valuable criteria.

Let me know if that works for you (and make sure you change the "Lead_Rating" to whatever your field's name is and on the right object).

Best,

Chris

[wkuehler]

Chris

That formula takes the numeric value sum of each row and divides it by the number of records for that row.  What I need is the number of records for each row divided by the total number of records returned.

Any ideas?

-wkuehler

[chris925]

Could you add a hidden field whose default value is 1 (so it's always populated), but sum on that (so it adds 1+1+... 9 times to get a total of 9, and then divide/percent each group by the total?

[wkuehler]

The problem is that there is no way to get an overall total, only a total for each row.  What I need is a variable that is the total number of rows returned by the report.  Lets call it {returned_rows_total}

{rowcount} / {returned_rows_total}

[enricof]

Chris,

have you ever got an answer?

I'm struggeling with the same problem and getting no results.

Thanks

Cheers

[jjuez]

You want this:

Overall Rating Number: 1 (9 records)               26%
Overall Rating Number: 2 (7 records)               20%
Overall Rating Number: 3 (6 records)               17%
Overall Rating Number: 4 (13 records)             37%
        Grand Totals (35 records)

If the Overall Rating Number is a picklist object, try the following:

1. Create 4 formula fields (setup > Lead > fields). You can call them RatingNumber1, RatingNumber2, etc

   Each formula should be like this:

RatingNumber1

   CASE(Overall_Rating_Number__c , "1", 1, null)

RatingNumber2

   CASE(Overall_Rating_Number__c , "2", 1, null)

RatingNumber3

   CASE(Overall_Rating_Number__c , "3", 1, null)

RatingNumber4

   CASE(Overall_Rating_Number__c , "4", 1, null)

(You can hide this formula fields so that they do not appear in your page layout, as you do not really need them there)

Then, in the report, create a summary report and group by Overall Rating number

In the "columns to totals" step, add 4 new custom summary formulas:

Label: %ORN1

Format: %

Formula: Lead.RatingNumber1__cUM/RowCount

Label: %ORN2

Format: %

Formula: Lead.RatingNumber2__cUM/RowCount

Label: %ORN3

Format: %

Formula: Lead.RatingNumber3__cUM/RowCount

Label: %ORN4

Format: %

Formula: Lead.RatingNumber4__cUM/RowCount

And that's pretty much it. It is not perfect 100%, but is is the furthest I have been able to get.

The idea behind this approach is that the lead will have 4 new (hidden) fields. When you choose an Overall Rating Number, the correspondant field will get a value of 1, and the other 3 hidden fields will get a null.

Finally, each one of the last formulas are adding up the ones for that specific hidden field and dividing the sum by the total ammount of leads, giving you a percentage.

I hope this helps

By the way, below you can see what I've got:

5 cases, 2 for EMEA region (40%), and 1 for each of the other 3 regions (20% each).

Case Number    Status        Region          %EMEA     %NA      %LA     %Asia

00001033            New            EMEA

00001034            New            EMEA

00001035            New            North America

00001036            New            Latin America

00001037            New            Asia 

Grand Totals (5 records)                               

                                                                          40%        20%      20%      20%

Hiding details:

                                                %EMEA     %NA      %LA     %Asia

Grand Totals (5 records)                               

                                                     40%        20%      20%      20%

Grouped by Region:

                                                                    %EMEA     %NA    %LA    %Asia

Region: EMEA (2 records)                        

                                                                         100%        0%       0%      0%
Region: North America (1 record)

                                                                            0%      100%     0%      0%
Region: Latin America (1 record)

                                                                            0%         0%     100%   0%
Region: Asia (1 record)

                                                                             0%         0%       0%    100%

Grand Totals (5 records)                               40%         20%     20%    20%

[mh6788]

It still doesn't seem possible to do this easily with native functionality (outside of the workaround proposed earlier, which is somewhat cumbersome to implement).   Here are some Ideas to vote on that might help:

Reports Custom Formulas

http://ideas.salesforce.com/article/show/72840/Reports_Custom_Formulas

Pie Charts with Percentage Values Displayed

http://ideas.salesforce.com/article/show/75939/Pie_Charts_with_Percentage_Values_Displayed

Cheers,

MH

[jjcrowner]

Thank you for posting this -- I was able to use it.  One caveat, though -- you are only allowed to create 5 custom summary formulas on an individual report, so if your picklist has greater than 5 choices, you will not be able to include them all.

[dmitrybelakhov · Accepted solution]

Here's a more generic way of calculating percentages in a report summary formula for N number of categories

1. Create a custom formula number field on the object, e.g. "Record Exists", set equal to 1 (no conditions in the formula)

2. Use this summary formula in the report:

Object__c.Record_Exists__cUM / PARENTGROUPVAL(Object__c.Record_Exists__cUM, ROW_GRAND_SUMMARY, COLUMN_GRAND_SUMMARY)