7 Replies Latest reply on Feb 6, 2018 8:26 AM by Leandro Nicolás

    How to average latest 3 months percentage?

    Yew Seng Lai

      Hello, I have 3 measures as below.

       

      MthSales Target% (Sales/Target)
      Jan1000150066.67%
      Feb2000250080%
      Mar2500300083.33%
      Apr2800300093.33%

       

      And then I would like to have another result as below

      MthAvg Sales - latest 3 mthAvg Target - latest 3 mthAvg % (Sales/Target) - 3 mth
      3 mths2433.332833.3385.55%

       

      How can i average the latest 3 months (Feb > Apr)? (80% + 83.33% + 93.33%)/3=85.55%. Can I just simply create a calculated field in tableau?

       

      I have tried to created by using the %(Sales/Target) variable, i put in the formula as avg ([%(Sales/Target)]), but it ended with error.

       

      Then I used another workaround way, which is sum of latest 3 months of Sales and Target, then divide by 3

      Sales (2000+2500+2800)/3 =2433.33 (A)

      Target (2500+3000+3000)/3 - 2833.33 (B)

       

      A/B= 85.88%, I thought this formula will produce the same result, but in fact is not...both % are different a bit..

       

      Above just a example, my real data is actually different by 1%.

       

      Anyone can advise? 88.55% is the figure that I want.

        • 1. Re: How to average latest 3 months percentage?
          Mark Fraser

          Hi Yew

           

          I have to admit I didn't read your question really thoroughly, is the expectation to calculate the % in a different way - you have 2 percentages - 85.88% and 88.55%?!

           

          Anyway, I have something working for you (the 85.88%) but its not currently dynamic, yet.

          Here is what I have so far -

          Formulas

          Sales/Target >> SUM([Sales])/SUM([Target])

           

          Period Sales >> LOOKUP(RUNNING_SUM(SUM([Sales])),LAST())-LOOKUP(RUNNING_SUM(SUM([Sales])),FIRST())

           

          Period Target >> LOOKUP(RUNNING_SUM(SUM([Target])),LAST())-LOOKUP(RUNNING_SUM(SUM([Target])),FIRST())

           

          Perc% then is simply >> [Period Sales]/[Period Target] (they are already aggregated)

           

          As mentioned, it is not a dynamic solution. Id like to use parameters to control the number of months to look back.

          This solution uses the FIRST() and LAST() rows, and so as the data increases, the formulas will update but I'm not sure if you're looking for a rolling calculation or fixed period.

          Hope something above helps!

           

          9.3.1 workbook attached.

           

          Cheers

          Mark

          • 2. Re: How to average latest 3 months percentage?
            Yew Seng Lai

            Hi Mark,

             

            Thanks for the formula, but the % that i want is 85.55%, not the 85.88%. If you put avg up the latest 3 mths % in excel, it will give you 85.55%...possible to get 85.55%?

             

            Yes, it is a fixed period, always looking for 3 mths latest. My data is always 15 mths data, so I think I just minus 12 instead of 1..

            • 3. Re: How to average latest 3 months percentage?
              Mark Fraser

              Hi Yew

               

              I see what you mean...

              You need both the average (7300/8500 = 85.88%) as well as the average of Sales/Target ((2000/2500)+(2500/3000)+(2800/3000))/3 = 85.55%)

               

              Just how to do it?!

               

              I'm sure it can be done, but everything I have done so far hasn't been pretty.

              FYI - I am trying both FIRST() and LAST() as well as WINDOW_AVG and/or RUNNING_AVG, maybe INDEX() and/or LOOKUP.

              The answer is somewhere in there I'm sure!! Fun problem

               

              I'll also ping Simon Runc

              I have the D6 figure, its the E5 figure ((2000/2500)+(2500/3000)+(2800/3000))/3 = 85.55%) we are trying to achieve... hopefully dynamically.

              Cheers

              Mark

              • 4. Re: How to average latest 3 months percentage?
                Simon Runc

                hi Mark,

                 

                Thanks for the ping...I've only had a quick read through, and have come up with this formula

                WINDOW_AVG([Sales/Target],-2,0)

                 

                and then used compute using = month...As you can see we get the 85.56% against the last month. This is dynamic in that it runs the last 3 for every month (rolling), but we could add a condition (or Last(), First()...etc.) if we only want this for the last month?

                 

                Looking at all the RUNNING_SUM work to get these targets...feel a little like Ruud van Nistelrooy (with your base country and euro-2016 round the corner...hope you appreciate the reference) in that...all the hard work has been done, I come in put it in the back of the net (from 2 yards!!)

                1 of 1 people found this helpful
                • 5. Re: How to average latest 3 months percentage?
                  Mark Fraser

                  Thank you Simon, I was hovering around that, but kept over complicating it, you star!

                  Its been too long since I got involved in some nice table calcs, I have to admit, I enjoyed it!

                   

                  Happy for me to lay it up, and you to score

                   

                  Alas, no Oranje army at the Euros!!

                  None of this >> https://www.google.nl/search?q=bavaria+jurkje&espv=2&biw=1920&bih=995&tbm=isch&tbo=u&source=univ&sa=X&sqi=2&ved=0ahUKEwj…

                  That said, I will always support England, I enjoy the pain of disappointment and penalties too much!! haha

                   

                  @Yew

                  As Simon recommends, swapp the Perc% calc to WINDOW_AVG([Sales/Target],-2,0) (set to month)

                  And as Simon also recommends, if you only want the last month, swap the formula to

                  IF LAST() == 0 THEN WINDOW_AVG([Sales/Target],-2,0) END

                  Hope that helps!

                   

                  Cheers

                  Mark

                  1 of 1 people found this helpful
                  • 6. Re: How to average latest 3 months percentage?
                    Simon Runc

                    I'd forgotten about that...I just assume they always qualify...you will be missed (I'm, of course, referring to the Total Football....and not the 'Fans' in your link!! - how very Dutch!)

                    Hopefully we can make it far enough that penalities are even an option!! (not made it out of the group stages for a while!!)

                     

                    Yes there is a real satisfaction to getting a table calc to sing & dance!!

                    • 7. Re: How to average latest 3 months percentage?
                      Leandro Nicolás

                      It is not right to make an average out of percentages. At least not mathematically.

                      You have to sum the 3 months of Sales and then divide by the 3 months of Target.