Forum Discussion
How to make average variance with week number
Here is the question:
I'd like to show every week average as below:
Week01 2
Week02 2.5
Week03 3
Week04 6
I want to show: Week01 =2; Week02 =average(week01,week02)= 2.25; Week03= average(week01, week02,week03) = 2.5; Week04= average (week01, week02, week03, week04) = 3.375.
How to make this happen. thanks a lot.
You could do this with a measure like the following
Running Avg = AVERAGEX( Filter(all('Table'),'Table'[Week] <= max('Table'[Week])), 'Table'[Amount])If you want to do a running total within the categories like that I think we'd need to retain the category filters which we could do with an expression like the following:
Running Avg = var catTable = VALUES('Table'[Category]) var dateTable = Filter(ALL('table'),'Table'[Week] <= max('Table'[Week]) && 'Table'[Category] IN catTable) return AVERAGEX( dateTable, 'Table'[Amount])it really does work. Thank you for your professional answer!
I've tried many times base on your previous answer, found that below formula also can make this happen:
Measure = calculate(average('Table'[Amount]),filter(ALLSELECTED('Table'),'Table'[Category]=max('Table'[Category])),FILTER(ALLSELECTED('Table'),'Table'[Week]<=max('Table'[Week])))
9 Replies
- d_gosbell
Super User
You could do this with a measure like the following
Running Avg = AVERAGEX( Filter(all('Table'),'Table'[Week] <= max('Table'[Week])), 'Table'[Amount])There is one more question, I forgot to add catogory in previous question, if still need consider catogory, then how to filter? thanks.
I tried, below formula will make all catogory has the same average.
Running Avg = AVERAGEX( Filter(all('Table'),'Table'[Week] <= max('Table'[Week])), 'Table'[Amount])Week Amount Catagory
01 2.00 A
02 2.50 A
03 3.00 B
04 6.00 B
- d_gosbell
If category is a column in 'Table' then using ALLEXCEPT instead of ALL as in the following might work
Running Avg = AVERAGEX( Filter(allexcept('Table', 'Table'[Category]),'Table'[Week] <= max('Table'[Week])), 'Table'[Amount])