Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TJohnsonOK
Helper I
Helper I

Finding the Avg of an Avg, Max of an Average and Min of an Avg, etc. in Paginated Reports

Hi--I have this matrix in paginated reports.  

TJohnsonOK_1-1713113825263.png

 

What I am wanting to do is find the Avg of the Avg, Avg of the Max and Avg of the Min of each of the columns Avg Max and Min in the matrix.

 

Then similarly in the Max line Max of the Avg, Max of the Max, and Max of the Min, etc.

I have tried several things, the last being 

=Avg(Fields!Heating_Degree_Days.Value, "Season6") (this gives the Average of the column), 

=Max(Fields!Heating_Degree_Days.Value, "Season6")(gives the max of the col)

etc, but does not give the avg of the Max).  

 

If I do this on each of the rows, it just duplicates the answers, which isn't what I am wanting.  I only have Heating_Degree_Days.value and Cooling_Degree_Days.value.  I do not have the Max, min and avg of these values in my dataset.  Everything else is working perfectly, just not for the avg max and min of each of the sets of totals.  Any assistance would be appreciated.  Thank you in advance.

 

 

3 REPLIES 3
v-kongfanf-msft
Community Support
Community Support

Hi @TJohnsonOK ,

 

In Power BI paginated reports , aggregates that compute aggregates directly in a single expression (for example, the average of the maximum values) are not supported due to the nature of aggregate functions. They are intended to work with a set of values, not with the results of other aggregation functions.

 

Given the complexity of calculating aggregates of aggregates directly in a paginated reports, I recommend browsing the following documentation for a more in-depth look at aggregate functions and their usage:

Aggregate functions reference for paginated reports - Microsoft Report Builder & Power BI Report Bui...

Expression scope for totals, aggregates, and built-in collections in a paginated report - Microsoft ...

 

Best Regards,
Adamk Kong

 

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

 

 

Hi AdamK

I apologize for the delay in responding to your response.  I have studied the links that you provided, but as I am not a programmer, I am not quite understanding what I need to do.  

Below is a picture of the matrix along with the row groups and column groups

TJohnsonOK_0-1713710326826.png

 

On the Avg row under the Avg Column, for HDD I have the following expression.

=Avg(Fields!Heating_Degree_Days.Value, "Season6")
for CDD, I have 

=Avg(Fields!Cooling_Degree_Days.Value, "Season6")

 

For the Max of the Max, and Min of the Min, I have similar expressions, which work.

TJohnsonOK_1-1713712858114.png

 

I can't determine what I need to do to find the Max of the Avg columns, Min of the Avg Columns, etc.

 

I found this blog, which I am trying to implement without success.

A link to the blog is 
https://www.magenium.com/resources/ssrs-reports/

My expression is 
=Sum(Sum(Max(Fields!Heating_Degree_Days.Value,"Season6"),"Gas_Month6"))


I receive this error.
The 'Value' expression for the text box 'Textbox94' specifies a scope that is not valid for a nested aggregate. The scope must be the same name of the scope specified by the outer aggregate or the name of a group or data region that is contained in the scope specified by the outer aggregate.
----------------------------
The definition of the report '' is invalid.

 

I have also reviewed this blog by SqlJason

https://sqljason.com/2010/07/aggregate-of-aggregate-function-in-ssrs.html, but I do not understand how to adapt the code to my needs.

 

Any assistance would be appreciated.

Any suggestions?  Again--I am not a programmer.  Thank you so much.

 

 

 

 

 




 

 

I am still working through this.  This is what I have so far that is working.

TJohnsonOK_0-1715187937588.png


The values that are not working yet is the Avg Column for the Max Row and the Avg column for the Min row.  The 4.16 that is in the Max row for the Average Column is not correct.  I am trying to find the Max Value for the Avg Column of the Max value.  Answer should be like this
2.93
2.93

3.70

7.15

3.55 --so the answer would be the max of those values 7.15

TJohnsonOK_1-1715188275623.png

 


this is the data value for the Max Row
=Max(Fields!Max_Price_MMBTU.Value) (circled in red)
then in the Avg column for that row, I have this
=Avg(Fields!Max_Price_MMBTU.Value) (circled in Green)

I want to find the Max of those values in the total area for the Max (Circled in Yellow)
I have tried about every combination I can think of, both with measures in power bi and trying to adjust the aggregations in paginated reports, and have not figured it out yet.  Any assistance would be appreciated.  Thank you




Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors