How to make average variance with week number

[Anonymous]

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.

[d_gosbell · Accepted solution]

You could do this with a measure like the following

Running Avg = AVERAGEX( Filter(all('Table'),'Table'[Week] <= max('Table'[Week])), 'Table'[Amount])

 

[d_gosbell · Accepted solution]

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

[d_gosbell · Accepted solution]

You could do this with a measure like the following

Running Avg = AVERAGEX( Filter(all('Table'),'Table'[Week] <= max('Table'[Week])), 'Table'[Amount])

 

[Anonymous]

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

[Anonymous]

Is my powerBI wrong?  I found that "allexcept" measure the same with "all" measure.

[d_gosbell]

You only have one category per week in that small sample set so you won't see any difference in those calcs. What result are you expecting to see?

[Anonymous]

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

[d_gosbell · Accepted solution]

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

[Anonymous · Accepted solution]

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

[Anonymous]

Thank you! d_gosbell!   superman you are. 🙂