Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Hope all is well.
I am working on a paginated report which contains a matrix made of
two Row groups having
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.
Please assist me if possible, I'll be grateful to you.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
8 | |
4 | |
2 | |
2 |
User | Count |
---|---|
12 | |
7 | |
7 | |
2 | |
2 |