Aggregation based on Average

[Anonymous]

I have the following table:

ID            Transaction Date

1              1/31/2018

2               1/31/2018

3               1/31/2018

4                2/28/2018

5                2/28/2018

6                12/31/2017

7                12/31/2017

8                 12/31/2017

I want to calculate the average based on the grouping them by trasaction date.

Ex: Count of ID when Transaction date = 1/31/2018 + Count of ID when transaction date = 2018 and so on.

The 2017 is an example. Ideally I would like it as count of ID groupin git by transaction date in that year. In 2017 it would be Measure = (count(ID) when transaction date =1/31/2017 + count of ID when transaction date = 2/28/2017 + Count of ID when transaction date= 3/31/2017 + .......)/12. As in the average.

For 2018, it needs to be b9 8 and similarly in September it needs to be by 8 and so on.

Can this be done in any way? Thank You

[v-yulgu-msft]

Hi @Anonymous,

You could create a measure:

Average = COUNT('Table'[ID])/12

Add 'Transaction Date' hierarchy (remove all levels except for 'Year') and above measure into table visual.

Regards,

Yuliana Gu

[Anonymous]

I would need that as a seperate measure to show on my visuals. I should not be showing the transaction date as part of my visuals.