Forum Discussion
How to make average variance with week number
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])))
I expect to get as below:
Average should only be cal. in the same catogory. No calculation among different groups (catogory). PowerBI can realize this? thanks.
| Week | Amount | Catogory | ave. | |
| 1 | 2 | A | 2 | |
| 2 | 2.5 | A | 2.25 | |
| 3 | 3 | B | 3 | |
| 4 | 6 | B | 4.5 |
Super UserIf 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])))