Forum Discussion
Helper IAverage with Group by DAX
Hi
I have this data
myRate | myDate
7 | 1 Feb 2019
3 | 14 Feb 2019
9 | 1 Feb 2019
4 | 4 Mar 2019
6 | 11 Apr 2019
3 | 13 Mar 2019
8 | 9 Feb 2019
2 | 2 Apr 2019
6 | 1 Feb 2019
5 | 2 Apr 2019
1 | 21 Feb 2019
9 | 18 Mar 2019
3 | 30 Mar 2019
How in DAX I can get a new column that Shows this
New_Column = Average of Rates to the same months
my Daya will ook like this
myRate | myDate | myAvg
7 | 1 Feb 2019 | 5.66
3 | 14 Feb 2019 | 5.66
9 | 1 Feb 2019 | 5.66
4 | 4 Mar 2019 | 4.75
6 | 11 Apr 2019 | 4.33
3 | 13 Mar 2019 | 4.75
8 | 9 Feb 2019 | 5.66
2 | 2 Apr 2019 | 4.33
6 | 1 Feb 2019 | 5.66
5 | 2 Apr 2019 | 4.33
1 | 21 Feb 2019 | 5.66
9 | 18 Mar 2019 | 4.75
3 | 30 Mar 2019 | 4.75
hi, AkrMus
If you want it could be filtered by myRate , just try this formula
Measure = calculate(average( 'Table'[myRate]), FILTER(ALLSELECTED('Table'),'Table'[Month]=MAX('Table'[Month])))Result:
Best Regards,
Lin
5 Replies
AkrMusThanks,
But when I fliter through Slicer, the average still for the whole data without filter!!
How can I fix that to consider filter when find the average of the month?
v-lili6-msftCommunity Support
hi, AkrMus
First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/Second, for your requirement,
you need to add a month column (or year-month column)
Then use “all” functions to create a measure
Measure = calculate(average( 'Table'[myRate]), ALLEXCEPT('Table','Table'[Month]))Then drag month field into table visual( set this field don't summarize)
Result:
and here is pbix file, please try it.
Best Regards,
Lin
