5 Replies Latest reply on Nov 18, 2016 1:48 PM by djay temp

    Average by group

    Heather Fingalson

      I'm looking to get an average total amount by month.  The data will have several records ber day.

      for instance

      dateamount
      1/1/20111.5
      1/1/20111.6
      1/1/20111.1
      1/2/20111.5
      1/2/20111.8
      2/1/20111.5
      2/1/20111.5
      2/2/20111.7
      2/3/20111.9

      January total usage is 7.5, February total usage is 6.6. The result I want is the average of the 7.5 and 6.6= 7.05

      result should look like:

       

      Monthly Average 7.05

       

      I don't want to show any additional information other that the average. 

        • 1. Re: Average by group
          Tracy Rodgers

          Hi Heather,

           

          One way to do this is to create a calculated field for each calculation and then a third calc to find the average, i.e.:

           

          January:

          if datename('month',date)='January' then amount end

           

          February:

          if datename('month',date)='February' then amount end

           

          Average of Both:

          (sum([January])+ sum([February]))/2

           

          Then, place Measure Names and Measure Values on the view with these three calculations on the Measure Values card.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Average by group
            Heather Fingalson

            Thank you I was able to get this to work by creating a calculated field:

            sum([amount])/countd(datetrunc ('month',[date]))

            1 of 1 people found this helpful
            • 3. Re: Average by group
              Aishwarya Ragavendiran

              Thank you for this awesome solution.

              • 4. Re: Average by group
                Norbert Maijoor

                Hi Heather,

                 

                Many solutions already provided but find below my approach based on LOD-expression and stored in attached workbook version 9.2. Proofs...there are many ways to Rome;)

                 

                1 of 1 people found this helpful
                • 5. Re: Average by group
                  djay temp

                  I apologize for tagging my question to yours but I need similar aggregation. My data looks like below and i need to filter records before aggregating them. In this example below, i have two types of transaction, 'INQ' and 'UPD' and I am only interested in 'INQ' transactions so i need to filter on that. Also, I can have multiple samples for a given 'hour' for a "tran type" (notice two rows for INQ on 0 hour for 1/1/2016). I need a median and average tran count for any given hour (expected output).

                   

                  I tried to create calculate field as "Tran_by_Hour" -->  {FIXED [Hour] : SUM([Tran_Count])} but it is not giving correct result. My understanding (and i could be totally wrong) is that, i am getting incorrect result because filtering is not applied when aggregation takes place.

                   

                  Sample Data

                  date                hour      tran count     tran type    

                  1/1/2016          0          13                 INQ

                  1/1/2016          0          15                 INQ

                  1/1/2016          0          20                UPD

                  1/1/2016          1          13                INQ

                  1/1/2016          1           05               UPD

                  1/2/2016          0          12                INQ

                  1/2/2016          0           21               UPD

                  1/3/2016          0           35               INQ

                  1/4/2016          0            06               UPD

                   

                   

                  expected output for plotting

                  hour               median(tran count)         average(tran count)

                  0                    20                                       18.75

                  1                    0                                          3.26