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
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)
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
- 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