topic Re: Calculate Average Transactions Per Year in DAX Commands and Tips

Calculate Average Transactions Per Year

mp390988 — Tue, 13 Jun 2023 18:50:41 GMT

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

Re: Calculate Average Transactions Per Year

Jihwan_Kim — Wed, 14 Jun 2023 03:19:27 GMT

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

Re: Calculate Average Transactions Per Year

mp390988 — Wed, 14 Jun 2023 13:56:38 GMT

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

Re: Calculate Average Transactions Per Year

Jihwan_Kim — Wed, 14 Jun 2023 17:49:31 GMT

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.