Nested weighted averages in Report Builder

[Anonymous]

I'm developing a paginated version of an existing PowerBI report, and running into an issue replicating a table involving nested weighted averages. The original report used a lot of measures whose behavior isn't being replicated in Report Builder and that I've had to write new expressions for.

The basic format of the table is as follows, with each level being a weighted average of the previous levels according to the percentage near them. So, the Group Metric score is [Metric_Value] averaged by [Group_Metric_Weight], then Performance Metric score is the average of those values weighted by [Performance_Metric_Weight], and the total score in the leftmost column is the average of those values weighted by [Total_Weight].

With the row groups set up as such:

The rightmost column gives me the correct numbers by just using the [Metric_Value] field with no aggregations, and the first weighted average works fine with those row groups and this expression:

=Sum(Fields!Group_Metric_Weight.Value * Fields!Metric_Value.Value) / Sum(Fields!Group_Metric_Weight.Value)

But I run into issues trying to get the next aggregate after that, since as far as I can tell there's no way to directly access the results of a previous aggregation? And with this expression, I get the incorrect results above:

=Sum(Fields!Perf_Metric_Weight.Value *
(Sum(Fields!Group_Metric_Weight.Value * Fields!Metric_Value.Value, "Group_Metric_Name") / Sum(Fields!Group_Metric_Weight.Value, "Group_Metric_Name")))
/ Sum(Fields!Perf_Metric_Weight.Value)

Where the performance metrics should be 4.22, 4.78, and 4.74 (only the last one is correct, and I suspect this is because both group metrics are just the same as the base metrics), and the total score should be 4.49 instead of 4.31.

I've also tried nesting the averages directly, but that still resulted in incorrect values. My last idea, since at the moment the list of metrics is always the same, would be to just make every text box manually instead, but then I run into issues trying to filter the data to only have the metrics I want for each text box.

Is there something I'm missing to fix this expression or is this sort of behavior just not available to report builder?

[v-henryk-mstf]

Hi @Anonymous ,

I can't seem to reproduce your problem, try to refer to the following document to see if it helps you.

Nested data regions in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft Docs

sql - Add an overall average row to Report Builder report - Stack Overflow

Looking forward to your feedback.

Best Regards,
Henry

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