topic Re: Sum of average by month in DAX Commands and Tips

Sum of average by month

Lefuneste57 — Wed, 03 Jul 2019 16:00:05 GMT

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

Re: Sum of average by month

avanderschilden — Thu, 04 Jul 2019 18:16:47 GMT

What do you think about this?

Re: Sum of average by month

Lefuneste57 — Fri, 05 Jul 2019 07:09:39 GMT

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.

Re: Sum of average by month

avanderschilden — Fri, 05 Jul 2019 07:36:47 GMT

And what do you think about this result?

Re: Sum of average by month

Anonymous — Fri, 05 Jul 2019 09:03:59 GMT

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

Re: Sum of average by month

avanderschilden — Fri, 05 Jul 2019 09:13:58 GMT

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.

Re: Sum of average by month

Lefuneste57 — Fri, 05 Jul 2019 11:35:27 GMT

Thanks,

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

Thanks

Re: Sum of average by month

avanderschilden — Fri, 05 Jul 2019 11:48:07 GMT

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

Re: Sum of average by month

Anonymous — Fri, 05 Jul 2019 12:40:32 GMT

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

Re: Sum of average by month

Lefuneste57 — Fri, 05 Jul 2019 15:18:50 GMT

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.

:smileyhappy:

Re: Sum of average by month

Anonymous — Sat, 06 Jul 2019 09:56:48 GMT

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

Re: Sum of average by month

Lefuneste57 — Sat, 06 Jul 2019 14:11:14 GMT

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

:smileyhappy: