1 Reply Latest reply on Nov 2, 2018 7:57 PM by swaroop.gantela

    Avg of calculation error

    MIGUEL MENDOZA

      Hello folks

      I'm struggling trying to get a new calculation t get the avg of the column o variance%  per day and use this new field to create a graphic with this. As an example

      the calculated table of variance% is as follow and I want to calculate the average of those values (avg doesn't work just running and Window AVG work)

      Now if I calculate the average in excel I got: 9.00%,17.07% and 8.09%, but the graphic with the only formulas allowed are as follow:

      Please advise.

        • 1. Re: Avg of calculation error
          swaroop.gantela

          Miguel,


          I'm not sure, but I think it can be done with Window_Avg.

           

          I didn't have access to your data, so I typed in some of the values

          and validated what I thought was the wt. avg against excel.

          I may have guessed wrong on how you calculated it.

           

          Weighted Average:

          SUM([wt (286680avg)].[variance %])*SUM([Out Count])/(TOTAL(SUM([Out Count])))

           

          Daily average of wt avg:

          WINDOW_AVG([wt avg])

           

          For the chart, because [Product] is needed for the calculations, [Product] needs to go on

          the Detail shelf. This multiplies the copies of the correct value, so this can be reduced

          by putting Index on the Filter shelf and filtering to 1.

           

          The critical part is the table calculation setting shown below.

           

          Please see workbook v10.1 and excel sheet attached in the Forum Thread:

          Avg of calculation error

           

          286680avg.png