How to calculate average of sum for each month from all yeras

[adii]

How to calculate average of sum for each month from all yeras

Hello I'm wondering how solve problem with calculate average of sum in each month. 
I got 2 tables, first is Calendar 

Date	Month	Year
2021-01-01	1	2021
.....	...	...
2024-05-07	5	2024

Second table  which name is Data 

Date	Sales
2021-01-01	10
2021-01-02	15
.....	...
2024-05-06	13
2024-05-07	25

I Have TotalSales = SUMX(Data,Data[Sales])

I need recive table like below

Year	Month	Total Sales	Average sales of Month
2021	1	500	450
2021	2	600	800
...	...	...	...
2024	1	400	450
2024	2	1000	800
...	...	...

I Can resolve that problem but its require make a new table (SUMMERIZe, ...) , im looking something better tyo resolve that

Conclusion . 

I woul like recive measure which will be Avarage of Total Sales for each month from data.range. so avarage should  be the same for May 2023. 2024 etc 

Thanks a lot 

[adii]

I recived from measure Average Sales value equal  of total sales 

doesnt work correctly

[tamerj1]

@adii 

Yes you are right 

Average Sales =
AVERAGEX (
CALCULATETABLE ( VALUES ( 'Calendar'[Month] ), ALL ( 'Calendar'[Year] ) ),
CALCULATE ( [Total Sales], ALL ( 'Calendar'[Year] ) )
)

[adii]

still doesnt work . The same result 

[tamerj1]

Hi @adii 
Same result? Strainge!... Wrong result? Ture.

Average Sales = 
AVERAGEX (
    CALCULATETABLE ( 
        SUMMARIZE ( 'Date', 'Date'[Year],'Date'[Month Number] ), 
        ALL ( 'Date'[Year] ) 
    ),
    [Sales Amount]
)

[adii]

hi @tamerj1  im completely confused why its doesnt work for my case.
i put below screen of my formul and table result (righr down corner)

DAX is like a hell... in python I can do it in 2 sec...

[tamerj1]

@adii 

Don't use variable. Place the CALCULATE formula directly inside the AVERAGEX otherwise the context transition won't take effect 

[adii]

Still doesn't work ... 

 

[tamerj1]

@adii 

I don't see the table header. Are you placing the correct measure in the table?

[adii]

 my table 

[tamerj1 · Accepted solution]

@adii 

Seems to be working. Correct me if I'm wrong. However, referencing the complete table inside a CACLULATE filter brings up the complete extended table which incudes 'Calendar'[Date]. Including this cilumn in the filter context would break the formula. I would recommend using 

TEST =
AVERAGEX (
CALCULATETABLE (
SUMMARIZE ( 'Calendar', 'Calendar'[Year], 'Calendar'[Month] ),
ALL ( 'Calendar'[Year] )
),
CALCULATE (
SUMX (
FILTER ( Kontrakty, Kontrakty[gielda] = "TGE" ),
Kontrakty[wolumenJednostkowy]
)
)
)

[adii]

It's work ! Thanks ,

can you explain how come first method doesn't work ?  Second method with filter work correct ? I'm  confused 

[tamerj1]

@adii 
With CALCULATE wrapping our formula the SUMMARIZE table wich is composed of 'Calendar'[Year] & 'Calendar'[Month] is converted to a new filter context replacing the original table visual filter context which was also composed of the very same two columns. To explain further let's take the first row as per your last screenshot as an example:

The filter context of this row originally contained

Original Filter Context

Year	Month
2026	12

Then it is replace by the CLACULATETABLE - SUMMARIZE table due to the effect of context transition of the CALCULATE that wraps our formula inside the iterator function AVERAGEX. This overwriting of the filter context produced a new one as follows

CALCULATE Filter Context1

Year	Month
2026	12
2025	12
2024	12
etc..	12

Because we have removed the filter from the Year column using ALL and therefore we have now all the years not only 2026 and thus the formula placed inside CALCULATE is evaluated within this filter context as follows:

AVERAGEX will iterate this table (which contains as many rows as number of years available in the 'Calendar' table) row by row and evaluate the sum of the column Kontrakty[wolumenJednostkowy] for every row

Year	Month	Aggregation1	Filter
2026	12	SUM(Kontrakty[wolumenJednostkowy])	Y2026 / M12
2025	12	SUM(Kontrakty[wolumenJednostkowy])	Y2025 / M12
etc..	12	SUM(Kontrakty[wolumenJednostkowy])	etc..

Then AVERAGEX will take the sum of Aggregation1 then divide over the number of rows to produce the average.

My latest formula will follow the same as above except that the SUM will aggregate only the rows where Kontrakty[gielda] = "TGE" 

The case when you introduce a filter inside CALCULATE which is already inside an iterator function, things become much more complicated. CALCULATE creates a new filter context out of the iterated table but it also creates a new filter context out of the filter argument inside it. Now when you filter a complete table inside CALCULATE, the resulted table becomes part of the filter context. When you refer to table like Kontrakty then the engine includes all the columns of this table along with all its extensions (the extended fact table includes also all the columns of all the dimension tables that are connected with it in a one-many unidirectional relationship) in another filter context which will then be intersected with the one it created the first time while iteration the summary table. 

The problem is that table (Kontrakty) is evaluated in the outer filter context which is the very original filter context of the visual itself which is composed of year 2026 and the month 12.

And given that the columns of the 'Calendar' table are part of the extended (Kontrakty) table and that Year and Month are columns in the 'Calendar' table and that they are evaluated in the outer filter context, then the 2nd filter context that CALCULATE creates is

CALCULATE Filter Context 2

Year	Month
2026	12

The intersection between CALCULATE Filter Context 1 and CALCULATE Filter Context 2 is 

Final CALCULATE Filter Context

Year	Month
2026	12

As you can see we're back to the original filter context 😄

Probably you did not understand any word of all above which is totally normal. It is really unnecessarily complicated.
Long story short, try to avoid using CALCULATE as mush as you can and rather rely more on a NoCALCULATE approach introduced by @Greg_Deckler 

[adii]

It's difficult but I see the point 😉 thanks you so much 

[tamerj1]

@adii 
 Worth mentioning that the following should work 😄

TEST =
AVERAGEX (
    CALCULATETABLE (
        SUMMARIZE ( 'Calendar', 'Calendar'[Year], 'Calendar'[Month] ),
        ALL ( 'Calendar'[Year] )
    ),
    CALCULATE (
        CALCULATE (
            SUM ( Kontrakty[wolumenJednostkowy] ),
            FILTER ( Kontrakty, Kontrakty[gielda] = "TGE" )
        )
    )
)

[tamerj1]

Hi @adii 

Please try

Average Sales =
AVERAGEX (
CALCULATETABLE ( VALUES ( 'Calendar'[Month] ), ALL ( 'Calendar'[Year] ) ),
[Total Sales]
)