5 Replies Latest reply on Aug 27, 2010 8:49 AM by philmodjunk

    calculating percentage of total in report



      calculating percentage of total in report


      I attached a report that shows subtotals of 3 categories for a list of names. I'd like to also display the percentage of the total that each category represents by name (i.e., total would be 100% for each individual name.) I've played with a couple of different summary fields but nothing is coming out right.


      PART | fields


      SUBSUMMARY BY TYPE | type   type count

      SUBSUMMARY BY NAME | name count


        • 1. Re: calculating percentage of total in report

          GetSummary ( SummaryField ; Type) / GetSummary( SummaryField ; Name )

          Format the field as a percent in the inspector and you should get what you need provided you specify the correct break fields and sort your records to match.

          • 2. Re: calculating percentage of total in report

            is there a way to set up the report so that the types are in columns rather than rows?

            would look like

            .......... TYPE1 TYPE2 TYPE3

            Name1  % .......%...... %

            Name2 %........ % .......%


            • 3. Re: calculating percentage of total in report


              If you set up a table where you have one record for each "Name" you can either set up three relationships for the three values or one relationship and a filtered portal (Only in FileMaker 11) to display the separate subtotals in columns.

              Personnel::PersID = FacultyOnly::PersID AND
              Personnel::cFacultyOnly = FacultyONly::Type

              Personnel::PersID = NPP::PersID AND
              Personnel::cNPP = NPP::Type

              Personnel::PersID = Resident::PersID AND
              Personnel::cResident = Resident::Type

              Personnel::PersID = ReportTable::PersID

              FacultyONly, NPP, and Resident would be different table occurrences of the same ReportTable--the table used in the report you posted above.

              Count (FacultyOnly::PersID)/Count (ReportTable::PersID)

              Would then compute the percentage of "Faculty Only" records for a given record in Personnel.

              • 4. Re: calculating percentage of total in report

                Using relationships in a report makes it impossible (or, at least, hard) to limit the report to a found set. So I'd stick to what's possible to do with summaries and GetSummary.

                BTW, there's a special Fraction of Total type of summary, that can be subtotaled by any break field. But it's not much different than using the suggested calculation.

                The usual technique to show items side by side is to make calculated fields for each item type and summarize them separately. Since FileMaker can summarize individual repetitions, it's possible to use a single repeating field and a single repeating summary for this. I made a quick example:

                The file is here.

                • 5. Re: calculating percentage of total in report

                  Good suggestion! I had forgotten this approach and it's less trouble to set up as well.