Help Needed - is it possible to display a calculated rolling average of combined values?

[BenDC]

Hi,

I am using PowerBI to model weekly figures of services delivered. These services are split into categories. I have reviewed previous posts on the community and found a useful measure to calculate a rolling average for each category.  My data is structured in a simplified table that captures Category, Week Commencing Date and Quantity delivered of that category in the given week by using grouped data:

I've created a report page with slicers to enable date range and categories to be selected. I then have a graph and table, which are fine. I have 2 cards; one provides the total overall quantity of all the data in the selection. The other is intended to display the average of all the data in the current selection, but will remain blank unless only 1 category is selected at a time.

The measure I used, from a previous post, uses this formula:

Rolling Average = 
AVERAGEX(
    FILTER( ALLSELECTED('Reporting - Devices Weekly' ),
        'Reporting - Devices Weekly'[Category] = SELECTEDVALUE ( 'Reporting - Devices Weekly'[Category] )
            && 'Reporting - Devices Weekly'[Week Start] <= MAX( 'Reporting - Devices Weekly'[Week Start] ) ),
                    'Reporting - Devices Weekly'[Quantity] )

I believe I understand at a very simple level, that although I am benefitting from the SELECTEDVALUE when you look at my matrix below the graph, It probably isn't capable of collating the data from all the categories selected.

In this example, I have 5 categories selected. The combined total quantity of those categories for the week commencing 07 January 2022 is 80, the previous week is 62, the previous week 101, etc. Is it possible to calculate the average or rolling average of this combined total quantity of the selected categories and display this on my report as the selection changes from the user?

[v-yalanwu-msft]

Hi, @BenDC ;

It is difficult to judge the cause from the information you provide，

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Best Regards,
Community Support Team_ Yalan Wu

[v-yalanwu-msft]

Hi, @BenDC ;

You could try it.

Rolling Average2 = 
AVERAGEX(
    FILTER( 'Reporting - Devices Weekly',
        'Reporting - Devices Weekly'[Week Start] <= MAX( 'Reporting - Devices Weekly'[Week Start] ) ),
                    'Reporting - Devices Weekly'[Quantity] )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BenDC]

Hi @v-yalanwu-msft,

Thank you very much for looking at this. It's the first time I've been able to display a rolling average with multiple categories selected, so it is a great step forward, however, it is not calculating the average based on the combined weekly total quantities in the selection.

For example, I've filtered to 2 categories over 4 weeks.

Week Start Date	24 December 2021	31 December 2021	07 January 2022	14 January 2022	Totals
Category	Quantity	Average	Quantity	Average	Quantity	Average	Quantity	Average	Quantity	Average
Total Quantity	86	86	48	67	34	56	17	46.25	185	46.25
Ascom Myco3 - Order	86	86	28	57	22	45.33	17	38.25	153	38.25
Samsung Xcover5 - Order			20	20	12	16			32	16

Using this formula, it produces an overall average of 30.83:

I haven't been able to work out how this figure is calculated.

[amitchandak]

@BenDC , Try like

Rolling Average =
AVERAGEX(
FILTER( ALLSELECTED('Reporting - Devices Weekly' ),
'Reporting - Devices Weekly'[Category] = MAX ( 'Reporting - Devices Weekly'[Category] )
&& 'Reporting - Devices Weekly'[Week Start] <= MAX( 'Reporting - Devices Weekly'[Week Start] ) ),
'Reporting - Devices Weekly'[Quantity] )

What is expected behaviour and value ?

[BenDC]

Hi @amitchandak ,

Thanks for responding so quickly!
So I get an average of 2.50 from this measure:

I believe the MAX criteria is pulling the average of the last category in alphabetical order, which matches the average in the matrix for that category, so in other words, it is pulling the average of one of the selected categories.

[amitchandak]

@BenDC , I doubt you need

'Reporting - Devices Weekly'[Category] = MAX ( 'Reporting - Devices Weekly'[Category] )

reason for that ?

because when select a value for 'Reporting - Devices Weekly'[Category], because of allselected, it will be filtered

[BenDC]

In all honesty, I copied the formula and found it worked, I am not entirely sure how the logic works. I've removed this part of the measure and it now returns a value of 29.89. That might be taking an average of the quantity across the categories, and then averaging that. 

I need it to take a sum of the quantities and then average, the value should be 137.