Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
adii
Helper I
Helper I

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

 

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 

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

View solution in original post

15 REPLIES 15
adii
Helper I
Helper I

I recived from measure Average Sales value equal  of total sales 

doesnt work correctly

 

@adii 

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.

1.png

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

IMG_3257.jpeg

@adii 

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

Still doesn't work ... 

IMG_3260.jpeg

@adii 

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

image.jpg

 my table 

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

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

 

tamerj1
Super User
Super User

Hi @adii 

Please try

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors