topic Re: How to calculate average of sum for each month from all yeras in DAX Commands and Tips

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

adii — Wed, 08 May 2024 17:48:03 GMT

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

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

tamerj1 — Wed, 08 May 2024 17:58:30 GMT

Hi adii

Please try

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

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

adii — Wed, 08 May 2024 18:49:27 GMT

I recived from measure Average Sales value equal of total sales

doesnt work correctly

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

tamerj1 — Thu, 09 May 2024 03:42:08 GMT

adii

Yes you are right

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

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

adii — Thu, 09 May 2024 06:08:34 GMT

still doesnt work . The same result

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

tamerj1 — Thu, 09 May 2024 06:56:07 GMT

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

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

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

adii — Thu, 09 May 2024 08:40:13 GMT

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...

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

tamerj1 — Thu, 09 May 2024 09:59:13 GMT

adii

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

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

adii — Thu, 09 May 2024 11:29:44 GMT

Still doesn't work ...

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

tamerj1 — Thu, 09 May 2024 11:48:17 GMT

adii

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

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

adii — Thu, 09 May 2024 12:58:55 GMT

my table

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

tamerj1 — Thu, 09 May 2024 13:11:14 GMT

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

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

adii — Thu, 09 May 2024 13:21:14 GMT

It's work ! Thanks ,

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

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

tamerj1 — Thu, 09 May 2024 15:05:01 GMT

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

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

adii — Thu, 09 May 2024 15:47:13 GMT

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

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

tamerj1 — Thu, 09 May 2024 15:57:17 GMT

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