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

[TJohnsonOK]

Hi--I have this matrix in paginated reports.  

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.

[v-kongfanf-msft]

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.

[TJohnsonOK]

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

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.

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.

[TJohnsonOK]

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

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

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