Calculate Average Transactions Per Year

[mp390988]

Hi,

I have the below visual and as you can see, my last column "Average Transactions Per Year" is meant to display the average over the year. So for example, for the year 2011 it should display =9830/9=1092.22 everywhere where year =2011 regardless of the month

I tried to create a mesaure called Average Transactions Per Year defined as follows:

Average Transactions Per Year =  CALCULATE(AVERAGEX(FactTable_Transaction,COUNTROWS(FactTable_Transaction)))  

The Month and Year fields come from my table called Calendar.

Can someone please help?

Thank You in advance

[mp390988]

Thank you for the reply!

I am confused. I don't understand the significance of this.

FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = MAX ( 'Calendar'[Year] ) 

 
When I execute this in DAX studio it gives me this:

which is the same as:

SUMMARIZE (
    'Calendar',
    'Calendar'[Month],
    'Calendar'[Month Num]
)

[Jihwan_Kim]

Hi,

Thank you for your message.

I think the difference is the filter context that is existing, and that is applied to the each row in the visualization.

[Jihwan_Kim]

Hi, I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

# transaction measure: = 
SUM(Data[Number of Transaction])

Avg transaction per year measure: =
IF (
    NOT ISBLANK ( [# transaction measure:] ) && HASONEVALUE ( 'Calendar'[Year] ),
    AVERAGEX (
        SUMMARIZE (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = MAX ( 'Calendar'[Year] ) ),
            'Calendar'[Month name],
            'Calendar'[Month number]
        ),
        CALCULATE ( SUM ( Data[Number of Transaction] ) )
    )
)