Sum of average by month

[Lefuneste57]

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

So i have this table :

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

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

and i want this :

I want the sum of each average por month..

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

Thanks

[avanderschilden]

What do you think about this?

[Lefuneste57]

 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 :

So the average value each month is :

The result with your Dax formule give :

But i want this result :

Thank for helping.

[avanderschilden]

And what do you think about this result?

[Anonymous]

@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

[avanderschilden]

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

[Lefuneste57]

Thanks,

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

Thanks

[avanderschilden]

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

[Lefuneste57]

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 :

And then :

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

[Anonymous · Accepted solution]

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

[Lefuneste57]

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

[Anonymous]

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