Forum Discussion
Helper IAverage with Group by DAX
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
Helper IThanks,
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 Supporthi, 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
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