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

[IK181]

Hi All, 

Hope all is well.

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

 two Row groups having

1. Main Category as a Parent Group and
2. 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.

[Anonymous]

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

[Anonymous]

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.

[Sahir_Maharaj]

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.