cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Regular Visitor

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

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?

7 REPLIES 7
Community Support

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

Community Support

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.

Regular Visitor

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.

Super User

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

Regular Visitor

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.

Super User

@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

Regular Visitor

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors