Forum Discussion

AsandaMk's avatar
AsandaMk
Regular Visitor
9 years ago

aggregating moving averages

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 shoul...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
AsandaMk
Regular Visitor
9 years ago

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's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi AsandaMk,

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

    Best Regards,
    Angelia