Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic rolling average based on slicer selection

Hello!   I have the following measure which gives me the rolling average of average sales per week:   Dynamic Rolling AVG = VAR virtual_table = SUMMARIZE ( FILTER ( ALL ...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Try the measures:

    Cumulative = 
CALCULATE(
    SUM(Sheet5[AVG SALES in PCS]),
    FILTER(
        ALLSELECTED(sheet5),
        Sheet5[WEEK] <= MAX(Sheet5[WEEK])
    )
)

//If you don't have [index] column, you can add it in Edit Queries.
Result = 
[Cumulative]/MAX(Sheet5[Index])

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

Hi Anonymous ,

 

Try the measures:

Cumulative = 
CALCULATE(
    SUM(Sheet5[AVG SALES in PCS]),
    FILTER(
        ALLSELECTED(sheet5),
        Sheet5[WEEK] <= MAX(Sheet5[WEEK])
    )
)

//If you don't have [index] column, you can add it in Edit Queries.
Result = 
[Cumulative]/MAX(Sheet5[Index])

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks v-lionel-msft ,

     

    Your solution definitely works. I had to tweek it a bit cause the data sample I provided was just that, a sample but my model is more complex therefore it wouldn't work however because of your solution I could manage it 🙂 

     

    In the end my original formula changed to the following:

     

    AVG Rolling Sales = 

VAR AVG Rolling = 
SUMMARIZE(
    FILTER(ALLSELECTED('CALENDAR'),
         'CALENDAR'[WEEK]<= MAX('CALENDAR'[WEEK])
    ),
    'CALENDAR'[WEEK], "Cumulative", [SALES AVG (pcs)]
)

return
    AVERAGEX(AVG Rolling, [Cumulative])

     

    Have a nice day and thanks once again!