Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Aggregation based on Average

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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    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's avatar
      Anonymous
      Not applicable

      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.