aggregating moving averages

[AsandaMk]

Hi All,

I need help if anyone can. On my PowerBI, i'm calculating a rolling 12 months average, which works perfectly on a an individual item, if i i need to group the items together(where i should now sum the averages), i'm struggling to get the sum of rolling averages. The scenario of what i'm trying to achieve  is explained below:

Your assistance will be highly appreciated

[v-huizhn-msft]

Hi @AsandaMk,

RollingProfSum and RollingCapAVG is what you want? If it is, please review the following steps.

1. I create the sample table.



2. Create a calculated column to transfer the text date to a number type, which is comparable.

Month = SWITCH(Table1[Date],"Jan",1,"Feb",2,"March",3) 

3. Create two measure using the formulas.

RollingProfSum = CALCULATE(SUM(Table1[Prof]),FILTER(ALLEXCEPT(Table1,Table1[Client]),Table1[Month]<=MAX(Table1[Month])))

RollingCapAVG = CALCULATE(AVERAGE(Table1[Capital]),FILTER(ALLEXCEPT(Table1,Table1[Client]),Table1[Month]<=MAX(Table1[Month])))

Create a table visual to display the result.

If this is not what you want, please share more details for further analysis.

Best Regards,
Angelia

[AsandaMk]

Hi Angelia,

Thank you for responding, however your results are not what i am looking for, if you look at my original screenshot(Those are manual workings in excel, which i'm trying to emulate in PowerBI). I am able to get the total for Company =450 which is SUMX(Company,RollingProfSum). trying to get the value on the the total for Capital, which when i work it out in excel it is 106.3(Some of the 3 client's rolling avg's).

I hope this makes sense.

Thanks,
Thanda

[v-huizhn-msft]

Hi @AsandaMk,

Have you resolved your issue? If you have, please mark the right reply as answer.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @AsandaMk,

It's still confusing, I know how you get the RollingCapAVG for Feb and March, but I don't know how to get the values in Jan. 

If you just get the value (26.6667+43+36.6667)=106.6, you juse need to calculate a measure using the formula.

sum=CALCULATE(SUM(Table[RollingCapAVG]),FILTER(Table,Table[Month]="Mar"))

Best Regards,
Angelia