Average with Group by DAX

[AkrMus]

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

[Anonymous]

Create a new column and put your column titles in the appropriate spots i have put in bold

Average = calculate(average( my rate column), ALLEXCEPT(table name, my date column)

[AkrMus]

Thanks,

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

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

[AkrMus]

Many thanks for your reply

I have tried the file you uploaded

the Mesaure average does not change based on the slicer 😞

I added slicer for myRate and filtered data for myRate > 5

there was no change on the calculated column or the Measure

[v-lili6-msft · Accepted solution]

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