3 Replies Latest reply on Jan 6, 2012 12:52 AM by Martin Luxhøj

    Average of average.....?

    Martin Luxhøj

      Hi experts,

       

      I am wondering if there is another way of doing an average calculation. My problem is that I have to calculate a sales price which I do the following way:

       

      IIF([SalesQuantity]=0,0,[NetTurnover]/[SalesQuantity])

       

      But in order to get it to show correctly in my graphs I have to average the measure....this seems odd to me.

       

      Is there a better way of doing this?

       

      P.S. the reason why I'm using Net Turnover is that there are some reductions that do not show in the price that is shown on the invoice.

       

      Thank you in advance

       

      BR

       

      Martin

        • 1. Re: Average of average.....?
          Shawn Wallwork

          Sample workbook available?

          • 2. Re: Average of average.....?
            Joe Mako

            How about:

             

             

            IIF(SUM([SalesQuantity])=0,0,SUM([NetTurnover])/SUM([SalesQuantity]))


            • 3. Re: Average of average.....?
              Martin Luxhøj

              Joe that did the trick! Thank you very much.

               

              I did some reading in the manual and found some extra info under the topic Aggregate Calculations:

               

              "About Aggregate Calculations

              Suppose you want to analyze the overall gross margin for every product in your data source.

              One way to do this is to create a new calculated field called Margin that is equal to the profit

              divided by the sales. Then you could place this measure on a shelf and use the predefined

              summation aggregation. In this scenario, Margin is defined as follows:

              Margin = SUM([Profit]/ [Sales])

              This formula calculates the ratio of profit and sales for every row in the data source, and

              then sums the numbers. That is, the division is performed before the aggregation. However,

              this is almost certainly not what you would have intended because summing ratios is

              generally not useful.

              Instead, you probably want to know the sum of all profits divided by the sum of all sales.

              That formula is shown below.

              Margin = SUM( [Profit]) / SUM([Sales])

              In this case, the division is performed after each measure is aggregated. An aggregate

              calculation allows you to create formulas like this."