topic Re: Power BI Report Builder - Total of Top N by Group Filtering in Matrix in Report Server

Power BI Report Builder - Total of Top N by Group Filtering in Matrix

IK181 — Sat, 15 Jul 2023 22:37:36 GMT

Hi All,

Hope all is well.

I am working on a paginated report which contains a matrix made of

two Row groups having

Main Category as a Parent Group and
Area as Child Group

and have a 1 column group of Report Date with a column named "current date" & "previous date".

The resultant needs to be top 10 Area which was easy to achieve but the issue is unable to calculate the Grand total of top 10 returning Areas.

Would really appreciate if anyone can assist me in this regard and let me know how to calculate the Grand Total of Top 10 Areas in a matrix.

Re: Power BI Report Builder - Total of Top N by Group Filtering in Matrix

Sahir_Maharaj — Sun, 16 Jul 2023 18:50:25 GMT

Hello IK181,

Can you please try the following:

1. Define a custom code function in the report:

Dim TotalTop10Areas As Integer = 0 Public Function AddToTotalTop10Areas(ByVal value As Integer) As Integer TotalTop10Areas = TotalTop10Areas + value Return value End Function Public Function GetGrandTotalTop10Areas() As Integer Return TotalTop10Areas End Function

2. Add a textbox to your report where you want to display the grand total. In the Value property of the textbox:

=Code.GetGrandTotalTop10Areas()

3. In the cell of the matrix where you want to calculate the top 10 areas, add a custom expression to handle the top 10 calculation

=Code.AddToTotalTop10Areas(Sum(Fields!ValueToSum.Value))

4. Sort the matrix rows by the sum of the values in descending order.

Should you need further assistance please don't hesitate to reach out to me.

Re: Power BI Report Builder - Total of Top N by Group Filtering in Matrix

Anonymous — Wed, 02 Aug 2023 23:05:46 GMT

Thank you for the assistance, Sahir.
However, the given solution isn't returning the filtered rows total.

I have tried your solution for Top 2 rows, keeping everthing quite simple.

This is the design view: I have chosen Branch Banking in the Main Category group only.

This is the resultant view: Sum should be 7 instead of 9.

Re: Power BI Report Builder - Total of Top N by Group Filtering in Matrix

Anonymous — Wed, 02 Aug 2023 23:07:00 GMT

Please assist me if possible, I'll be grateful to you.