3 Replies Latest reply on Jan 15, 2020 2:04 PM by madhuri.tanniru

    Avg Daily Amount


      I am trying to figure out an average daily back order amount for a group of materials, both each month and each quarter. I want to be able to see the daily avg for all materials together, or for each one separately.


      The average daily number is calculated as the total backlog amount for the period (month or quarter) divided by the number of snapshots taken in the period.


      My problem is that when I take out or filter on certain dimensions (for ex fiscal month or material number), the number of snaps is no longer accurate (see screen shots for the right number each month & quarter).

      Can anyone help with the best approach for this?


      Packaged workbook attached.


        • 1. Re: Avg Daily Amount

          Please see if this is what you are looking for






          • 2. Re: Avg Daily Amount

            This is not quite what I had in mind. What I want to do is to use the max number of snaps for the period.


            There will only be a daily snapshot for a material if there is an actual back order.


            For example:

            Material 123 was snapped 31 times in January and had a total backlog sum of $50,000. The Avg Daily should then be 50,000/31 = $1,613.

            Material 456 was only snapped 10 times in January and had backlog sum of $75,000. The Avg Daily should still be 75,000/31 = $2,419.


            In other words, I want the backlog sum to be divided by the total possible number of snaps for the period - not just the times that material was actually snapped.


            What is happening in the workbook right now, is, that as soon as I filter on a specific material number, it will only show the number of snaps for that specific material, and daily avg then gets calculated based on that.


            Makes sense?

            • 3. Re: Avg Daily Amount

              So, is this what you are looking for?