Annual average from monthly average

[squarecat]

Hello everyone, 

I have a report built on Excel, but management wants it to be migrated to Power BI. In Excel I was using a dynamic table to obtain the sum of all the values per year and per month, here I'm attaching some dummy numbers so you can understand what I want to do.

2021	21.914.143
ene	1.201.549
feb	1.558.156
mar	1.778.263
abr	1.556.027
may	1.531.298
jun	1.872.954
jul	2.014.762
ago	1.177.969
sep	1.933.642
oct	3.131.360
nov	1.549.479
dic	2.608.685

From this table i would just calculate the average of all months and that would be my yearly average. In this case, the number would be 1.826.179.

Now in Power BI I can't seem to be able to figure out how to calculate this number. In the best case, the measure created would be able to calculate the average by quarter and by year.

By year: 1.826.179

By quarter:  1.512.656

Can someone guide me to achieve this result? I want to clarify that I do have a calendar table built in the model if that helps.

[johnt75 · Accepted solution]

You can create a measure like

Avg =
AVERAGEX (
    VALUES ( 'Date'[Year month] ),
    CALCULATE ( SUM ( 'Table'[Amount] ) )
)

[squarecat]

Thank you! this worked, I just added .[month] for the measure to calculate what I needed, this is my final DAX

Avg =
AVERAGEX (
    VALUES ( 'Calendar'[Date].[Month] ),
    CALCULATE ( SUM ( 'Table'[Amount] ) )
)

[squarecat]

I also want to add that the number I obtained in the example by quarter refers to the Q1 (average of jan, feb and march)

Thanks in advance for any help!