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

 

Need Help

4 Replies

  • 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