Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 
20210101  1  2021 
.....  ...  ... 
20240507  5  2024 
Second table which name is Data
Date  Sales 
20210101  10 
20210102  15 
.....  ... 
20240506  13 
20240507  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
Solved! Go to Solution.
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]
)
)
)
I recived from measure Average Sales value equal of total sales
doesnt work correctly
Yes you are right
Average Sales =
AVERAGEX (
CALCULATETABLE ( VALUES ( 'Calendar'[Month] ), ALL ( 'Calendar'[Year] ) ),
CALCULATE ( [Total Sales], ALL ( 'Calendar'[Year] ) )
)
still doesnt work . The same result
Hi @adii
Same result? Strainge!... Wrong result? Ture.
Average Sales =
AVERAGEX (
CALCULATETABLE (
SUMMARIZE ( 'Date', 'Date'[Year],'Date'[Month Number] ),
ALL ( 'Date'[Year] )
),
[Sales Amount]
)
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...
Don't use variable. Place the CALCULATE formula directly inside the AVERAGEX otherwise the context transition won't take effect
Still doesn't work ...
I don't see the table header. Are you placing the correct measure in the table?
my table
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]
)
)
)
It's work ! Thanks ,
can you explain how come first method doesn't work ? Second method with filter work correct ? I'm confused
@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 onemany 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
It's difficult but I see the point 😉 thanks you so much
@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" )
)
)
)
Hi @adii
Please try
Average Sales =
AVERAGEX (
CALCULATETABLE ( VALUES ( 'Calendar'[Month] ), ALL ( 'Calendar'[Year] ) ),
[Total Sales]
)
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get handson experience, and win awesome prizes.
User  Count 

25  
20  
18  
17  
17 
User  Count 

34  
21  
19  
18  
10 