-
1. Re: Average by group
Tracy RodgersJun 4, 2012 3:41 PM (in response to Heather Fingalson)
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 Jun 4, 2012 3:53 PM (in response to Tracy Rodgers)1 of 1 people found this helpfulThank you I was able to get this to work by creating a calculated field:
sum([amount])/countd(datetrunc ('month',[date]))
-
3. Re: Average by group
Aishwarya Ragavendiran Oct 21, 2016 5:56 PM (in response to Heather Fingalson)Thank you for this awesome solution.
-
4. Re: Average by group
Norbert MaijoorOct 22, 2016 9:35 AM (in response to Heather Fingalson)
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;)
-
LOD approach.twbx 10.4 KB
-
-
5. Re: Average by group
djay temp Nov 18, 2016 1:48 PM (in response to Heather Fingalson)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