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

Thank you for this awesome solution.

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;)

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