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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Lefuneste57
Helper I
Helper I

Sum of average by month

Hi, sorry for my english, i'm french.

 

So i have this table :

Ma table.PNG

 

For each month, i calculate the average (row :moyenne mensuelle) from salaire so i have this :

Moyenne mensuelle.PNG

 

I will have the sum of average but the systeme give me this:

Il y a.PNG

 

and i want this :

Je veux.PNG

 

I want the sum of each average por month..

 

Can you help me please and give to me the good Dax formule ?

 

Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

Avg of Avg = 
VAR __avgPerYearMonthTeamPerson =
    ADDCOLUMNS (
        SUMMARIZE (
        	'DataTable',
        	'DataTable'[Year],
        	'DataTable'[Month],
        	'DataTable'[Team],
                'DataTable'[Person]
        ),
        "Average", 
        	CALCULATE (
        		AVERAGEX (
        			'DataTable', 
        			'DataTable'[Salary]
        		) 
        	)
    )
VAR __avgPerYearMonthTeam =
	ADDCOLUMNS(
		SUMMARIZE(
			__avgPerYearMonthTeamPerson,
                       'DataTable'[Year],
                       'DataTable'[Month],
                       'DataTable'[Team]
		),
		"Average2",
			CALCULATE(
				AVERAGEX(
					__avgPerYearMonthTeamPerson,
					[Average]
				)
			)
	)
var __avgPerYearTeam =
	ADDCOLUMNS(
		SUMMARIZE(
			__avgPerYearMonthTeam,
                       'DataTable'[Year],
                       'DataTable'[Team]
		),
		"Average3",
			CALCULATE(
				AVERAGEX(
					__avgPerYearMonthTeam,
					[Average2]
				)
			)
	)
var __avgPerTeam =
	ADDCOLUMNS(
		SUMMARIZE(
			__avgPerYearTeam,
                        'DataTable'[Team]
		),
		"Average4",
			CALCULATE(
				AVERAGEX(
					__avgPerYearTeam,
					[Average3]
				)
			)
	)
var __avg =
    AVERAGEX(
        __avgPerTeam,
        [Average4]
    )
RETURN
	__avg

Best

Darek

View solution in original post

11 REPLIES 11
avanderschilden
Resolver I
Resolver I

What do you think about this?

 

Capture.PNG

 

 Hi, thanks for answers but it's no good..

My problem is that i don't have the same number of personn each team.

 

So I have make 5 person in team 1 and 4 person in team 2; look like :

Ma table.PNG

So the average value each month is :

Moyenne.PNG

The result with your Dax formule give :

Resultat.PNG

But i want this result :

Capture.PNG

Thank for helping.

 

And what do you think about this result?

 

Capture.PNG

Anonymous
Not applicable

@avanderschilden : Mate, please note that you SHOULD NEVER use SUMMARIZE to do aggregations. This is well known in the DAX world. The SUMMARIZE function is very bad for performance and its behaviour is so complex that it's better to steer clear of it if you want to know what your formula calculates. Its only good use is for giving you distinct combinations of rows AND NOTHING ELSE. If you want to know more on this, please go to www.sqlbi.com and find the right articles about it by the two gurus, Alberto Ferrari and Marco Russo. Instead of using SUMMARIZE to do summaries, you should use a combination of SUMMARIZE+ADDCOLUMNS.

 

Best

Darek

@Anonymous  thanks for the info. I never had performance issues with SUMMARIZE the way I use it, and I don't see any complexity in it. However, I will check out the article you advice.

Thanks,

 

so, can you telle me what is the new Dax formule for me if we used SUMMARIZE+ADDCOLUMNS ?

 

Thanks

 

Hello,

 

This would be the new measure with better performance;

 

New Measure =
AVERAGEX (
    SUMMARIZE (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Table'[Year], 'Table'[Month], 'Table'[Team] ),
            "Average", CALCULATE ( AVERAGEX ( 'Table', 'Table'[Salary] ) )
        ),
        'Table'[Year],
        'Table'[Team],
        "Average2", AVERAGEX (
            ADDCOLUMNS (
                SUMMARIZE ( 'Table', 'Table'[Year], 'Table'[Month], 'Table'[Team] ),
                "Average", CALCULATE ( AVERAGEX ( 'Table', 'Table'[Salary] ) )
            ),
            [Average]
        )
    ),
    [Average2]
)

Thank you all,

I have a new row that named "Nature", this is the type of Salary.

 

So can you tell me the new Dax code to have this :

Capture3.PNG

And then :

Capture10.PNGSo for Each Team I must calculate the average per Person per Month Then 

I must calculate the average per Team per Month

I must claculate the average per Team per Year.

 

Thank you for helping.

Smiley Happy

 

Anonymous
Not applicable

 

Avg of Avg = 
VAR __avgPerYearMonthTeamPerson =
    ADDCOLUMNS (
        SUMMARIZE (
        	'DataTable',
        	'DataTable'[Year],
        	'DataTable'[Month],
        	'DataTable'[Team],
                'DataTable'[Person]
        ),
        "Average", 
        	CALCULATE (
        		AVERAGEX (
        			'DataTable', 
        			'DataTable'[Salary]
        		) 
        	)
    )
VAR __avgPerYearMonthTeam =
	ADDCOLUMNS(
		SUMMARIZE(
			__avgPerYearMonthTeamPerson,
                       'DataTable'[Year],
                       'DataTable'[Month],
                       'DataTable'[Team]
		),
		"Average2",
			CALCULATE(
				AVERAGEX(
					__avgPerYearMonthTeamPerson,
					[Average]
				)
			)
	)
var __avgPerYearTeam =
	ADDCOLUMNS(
		SUMMARIZE(
			__avgPerYearMonthTeam,
                       'DataTable'[Year],
                       'DataTable'[Team]
		),
		"Average3",
			CALCULATE(
				AVERAGEX(
					__avgPerYearMonthTeam,
					[Average2]
				)
			)
	)
var __avgPerTeam =
	ADDCOLUMNS(
		SUMMARIZE(
			__avgPerYearTeam,
                        'DataTable'[Team]
		),
		"Average4",
			CALCULATE(
				AVERAGEX(
					__avgPerYearTeam,
					[Average3]
				)
			)
	)
var __avg =
    AVERAGEX(
        __avgPerTeam,
        [Average4]
    )
RETURN
	__avg

Best

Darek

Hi,

thank you very much.

It's exactly what i want; it's nice.

Thanks to all people who has helping me too.

Have a good day

Smiley Happy

Anonymous
Not applicable

Here's a measure that does the same as the previous one but without the bad use of SUMMARIZE (which is still present in the above measure):

[Avg of Avg] =
VAR __DataTableOfAverages =
    ADDCOLUMNS (
        SUMMARIZE (
        	'DataTable',
        	'DataTable'[Year],
        	'DataTable'[Month],
        	'DataTable'[Team]
        ),
        "Average", 
        	CALCULATE (
        		AVERAGEX (
        			'DataTable', 
        			'DataTable'[Salary]
        		) 
        	)
    )
VAR __DataTableOfAveragesOverAverages =
	ADDCOLUMNS(
		SUMMARIZE(
			__DataTableOfAverages,
                        'DataTable'[Year],
                        'DataTable'[Team]
		),
		"Average2",
			CALCULATE(
				AVERAGEX(
					__DataTableOfAverages,
					[Average]
				)
			)
	)
var __finalAverage =
    AVERAGEX (
        __DataTableOfAveragesOverAverages,
        [Average2]
    )
RETURN
	__finalAverage

You might find this code more easily understandable as well.

 

Best

Darek

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.