March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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 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
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]
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
29 | |
20 | |
15 | |
14 | |
10 |
User | Count |
---|---|
62 | |
26 | |
25 | |
22 | |
15 |