Skip to main content
cancel
Showing results for 
Search instead 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

Reply
BenDC
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:

 

BenDC_0-1642064246881.png

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.

 

BenDC_1-1642064346616.png

 

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
v-yalanwu-msft
Community Support
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

v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1642399232615.png


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.

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 Date24 December 202131 December 202107 January 202214 January 2022Totals
CategoryQuantityAverageQuantityAverageQuantityAverageQuantityAverageQuantityAverage
Total Quantity8686486734561746.2518546.25
Ascom Myco3 - Order868628572245.331738.2515338.25
Samsung Xcover5 - Order  20201216  3216

 

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

BenDC_0-1642413230974.png

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

 

amitchandak
Super User
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 ?

Hi @amitchandak ,

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

BenDC_0-1642066244311.png

 

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.

@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

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.

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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