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

# Average by group

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

for instance

 date amount 1/1/2011 1.5 1/1/2011 1.6 1/1/2011 1.1 1/2/2011 1.5 1/2/2011 1.8 2/1/2011 1.5 2/1/2011 1.5 2/2/2011 1.7 2/3/2011 1.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

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

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

Thank you for this awesome solution.

• ###### 4. Re: Average by group

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

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