Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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(week0...
Need Help
d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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.

 

WeekAmountCatogory ave.
12A 2
22.5A 2.25
33B 3
46B 4.5
  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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