cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Average 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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Anonymous
Not applicable

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)

Helper I

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?

Community 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/

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.