average by group

[yukon]

Hello,

I'm start learning DAX. I'm using DAX to get below figure. But i have difficult to get correct figure. What i want to do is "Average Plan Sales Amout by Group (Year)".

Except result

Year	Country	Plan Sales Amount	Avg Amt.
2019	AU	19183659	11019810
2019	ID	6309526	9999328.875
2019	IN	9674217	10526443.57
2019	JP	7413826	10668481.33
2019	KR	21671488	11319412.4
2019	MY	8065277	8731393.5
2019	SG	12516877	8953432.333
2019	TH	7103830	7171710
2019	TW	7239590	7239590
2020	AU	1000	5000
2020	ID	2000	5500
2020	IN	3000	6000
2020	JP	4000	6500
2020	KR	5000	7000
2020	MY	6000	7500
2020	SG	7000	8000
2020	TH	8000	8500
2020	TW	9000	9000

Here is my DAX formula in Excel Data Model. I'm using Dax in excel data model.

Many thanks for your help.

[littlemojopuppy · Accepted solution]

Hola!

Try this...

=AVERAGEX(
	FILTER(
		Table1,
		Table1[Year] = EARLIER(Table1[Year])
	),
	Table1[Plan Sales Amount]
)

[az38 · Accepted solution]

@yukon 

is it some running average? it looks like you have an error in Excel

if you need average from all groups by year try a measure

Average Plan Sales Amout by Group (Year) =
CALCULATE(AVERAGE(Table[Plan Sales Amount]), ALLEXCEPT(Table, Table[Year]) )

or column from @littlemojopuppy  solution

[Anonymous · Accepted solution]

@yukon 

Average by group can be done in ways in DAX, like this above answers. However, I think the data you provided is not correct for some reason. How can you get different values when you average. Maybe check your excel files and also provide the formula you used so we can see what are you calculating to get that expected result.

Usually, you get average by group(year) with column or measures using:

RESULT = CALCULATE(AVERAGE([Plan Sales Amount]), ALLEXCEPT(Table,[Year]))

Best regards
Paul Zheng

[yukon]

@az38 ; @littlemojopuppy ; @amitchandak ; @Anonymous 

You all provide the AVG formula is corrrect. Excel file formula is wrong after i checked with the person who give me the file.

Thank you so much for sharing the formula. I have learn a lot from you all.

[littlemojopuppy · Accepted solution]

Hola!

Try this...

=AVERAGEX(
	FILTER(
		Table1,
		Table1[Year] = EARLIER(Table1[Year])
	),
	Table1[Plan Sales Amount]
)

[littlemojopuppy]

The difference between what I posted is that @az38 created a measure and I created a calculated column (which is what you seemed to want).  His will work if you want a measure, and I've shown mine works as a column.

[yukon]

@littlemojopuppy ,

Thank for your formula. What i'm trying to get below result

so sorry for my poor explanation

[littlemojopuppy]

Your average formula...what is it averaging?

[yukon]

@littlemojopuppy 

This is excel formula that i got from existing excel file and trying to convert as data model.

Year 2019

Year 2020

Many thanks

[amitchandak]

@yukon ,

Try like

calculate(average(table[plan sales amout]),filter(all(table), table[year]= max(table[year]) && table[country code]= max(table[country code])))

[az38 · Accepted solution]

@yukon 

is it some running average? it looks like you have an error in Excel

if you need average from all groups by year try a measure

Average Plan Sales Amout by Group (Year) =
CALCULATE(AVERAGE(Table[Plan Sales Amount]), ALLEXCEPT(Table, Table[Year]) )

or column from @littlemojopuppy  solution

[littlemojopuppy]

It's not a running average and it's doesn't seem to be an average through the rest of the group.  Can you share the formula that isn't the first one?  That might help figure out what it is.

[az38]

Hi @yukon 

try ALLEXCEPT

Average Plan Sales Amout by Group (Year) =
CALCULATE(AVERAGE(Table[Plan Sales Amount]), ALLEXCEPT(Table, Table[Year], Table[Country]) )