Forum Discussion

AG_PBI's avatar
AG_PBI
Frequent Visitor
1 year ago
Solved

Weekly running average

Hi,

I am new to Power BI and this is my first report. I have a dataset as follows:

Loan NumberSubmission WeekVolume
Loan111000
Loan212000
Loan312000
Loan423000
Loan523000
Loan631000
Loan733000
Loan834000
Loan932000

 

In the report dashboard I need to present it like this:

Sub WeekTotal VolumeWeekly Running Avg Vol
150005000
260005500
3100007000

I refeered a few WINDOW function videos, but not able to get the weekly running average. Please suggest.

Need Help
  • AG_PBI's avatar
    AG_PBI
    1 year ago

    I figured it out. In the RunningSum calculation, it should be ALL('submit') instead of ALL('submit'[Submission Week]). Then it worked. Thanks!

6 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi AG_PBI - create a measure for the total weekly volume as below 

    Total Volume =
    SUM('Table'[Volume])

     

    Now create one more measure to calculates the average of all weekly total volumes up to the current submit week.

     

     

    Weekly Running Avg Vol =
    VAR CurrentWeek = MAX('submit'[Submission Week])
    VAR RunningSum =
        CALCULATE(
            SUM('submit'[Volume]),
            FILTER(
                ALL('submit'[Submission Week]),
                'submit'[Submission Week] <= CurrentWeek
            )
        )
    VAR WeekCount =
        COUNTROWS(
            FILTER(
                ALL('submit'[Submission Week]),
                'submit'[Submission Week] <= CurrentWeek
            )
        )
    RETURN
        DIVIDE(RunningSum, WeekCount)


    )
    RETURN
    DIVIDE(RunningSum, WeekCount)

     

    shared the expected output in above snapshot. 

     

    Hope it works.

  • AG_PBI's avatar
    AG_PBI
    Frequent Visitor
    1 year ago

    Thanks for your swift feedback. But it is not working as expected. I split the code and found that 'RunningSum' is exactly same as 'Total Volume'. It is just giving the sum of that week itself instead of doing the total with previous week(s).

  • AG_PBI's avatar
    AG_PBI
    Frequent Visitor
    1 year ago

    I figured it out. In the RunningSum calculation, it should be ALL('submit') instead of ALL('submit'[Submission Week]). Then it worked. Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi AG_PBI ， hello ,rajendraongole1, thank you for your prompt reply!

      Congratulations on solving this issue and thanks for sharing your solution.

       

      Please remember to accept your solution as answer.

       

      It will do great help to those who meet the similar question in this forum.

       

      Thanks again for your contribution.

       

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    AG_PBI 

    New Measurs

    Total Volume = SUM('YourTableName'[Volume])
    Weekly Running Avg Vol = 
    AVERAGEX(
    DATESINPERIOD(
    'YourTableName'[Submission Week],
    MAX('YourTableName'[Submission Week]),
    -3,
    WEEK
    ),
    [Total Volume]
    )

    This should help you get the desired results in your Power BI report.

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    AG_PBI 

    New Measurs

    Total Volume = SUM('YourTableName'[Volume])
    Weekly Running Avg Vol = 
    AVERAGEX(
    DATESINPERIOD(
    'YourTableName'[Submission Week],
    MAX('YourTableName'[Submission Week]),
    -3,
    WEEK
    ),
    [Total Volume]
    )

    This should help you get the desired results in your Power BI report.

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn