Forum Discussion
average by group
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.
Hola!
Try this...
=AVERAGEX( FILTER( Table1, Table1[Year] = EARLIER(Table1[Year]) ), Table1[Plan Sales Amount] )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
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
11 Replies
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 Zhengaz38 ; 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.
Hola!
Try this...
=AVERAGEX( FILTER( Table1, Table1[Year] = EARLIER(Table1[Year]) ), Table1[Plan Sales Amount] )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.
Thank for your formula. What i'm trying to get below result
so sorry for my poor explanation
