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

    calculating percentage of total in report

    denno

      Title

      calculating percentage of total in report

      Post

      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.

      Layout: 

      PART | fields

      SUBSUMMARY BY NAME | name

      SUBSUMMARY BY TYPE | type   type count

      SUBSUMMARY BY NAME | name count

      Safari.jpg

        • 1. Re: calculating percentage of total in report
          philmodjunk

          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
            denno

            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
              philmodjunk

              Yes,

              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
                MikhailEdoshin

                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
                  philmodjunk

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