2 Replies Latest reply on Feb 18, 2015 9:37 AM by philmodjunk

    A question about averages and percentages

    Stu412

      Title

      A question about averages and percentages

      Post

      Hi there

      I have a simple demo I'm working on in order to understand how FM works with averages and percentages.  I have set this up with seven customers, each have sales, costs and a percent of costs to sales.

      In addition, each customer is part of either North, South, East or West.  I have filtered portals so that only the relevant customers appear in a compass point portal, along with their percentage of costs to sales.

      What I want to do is take an average of the percentages.  What's happening is that FM is taking the figures overall, and then averaging them, rather than average(PercentSumField), so in the case of two customers, one having 80%, one having 90%, the average is 88% and not 85%.  

      The 88% is due to the fact that FM is combining both customers' sales and costs FIRST, and then averaging the results, despite my asking it to average on the percent results.

      I'm certain there's a perfectly logical reason it does this, but that doesn't alter the fact I want 85% as my result as opposed to 88%, which may technically be correct, but for the purposes I need it, is completely wrong!

      Thanks

        • 1. Re: A question about averages and percentages
          schamblee

          Your calculation is effect by the table occurrence.  I assume you want  your total  based on the records that are in the portal, so your calculation needs to be displayed based on that table occurrence.

          • 2. Re: A question about averages and percentages
            philmodjunk

             I have filtered portals so that only the relevant customers appear in a compass point portal, along with their percentage of costs to sales.

            The portal filtering is the source of your trouble here. Calculations that reference the data in the portal's table (occurrence) from the context of your layout's record will ignore the filters and refer to all related records, as you have discovered.

            You'll need to change your approach in one of the following ways:

            Use a match field that specifies either North, South, East or West to control which records appear in your portal so that you can remove the portal filter.

            If you can figure out a summary field that correctly computes your values, you can add a second, one row portal with the exact same portal filtering as the original and put your summary field inside the row of this one row portal. This result is basically "read Only" as it will be difficult to refer to any such value in other calculations so the previous method may be the better option.

            You can also use a calculation field with ExecuteSQL and a WHERE clause that replicates the filtering effects of both your relationship and your portal filter expression to compute this value.