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


      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.:



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



          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!



          • 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