Forum Discussion
[REPORT BUILDER Help] Nested Subtotal
Hi all,
i start with my example values (with football club) :
| Club | Budget | Joueur | Salaire |
| PSG | 100 | Ibra | 20 |
| PSG | 100 | Messi | 40 |
| PSG | 100 | Mbappe | 50 |
| Bordeaux | 10 | Pauleta | 3 |
| Bordeaux | 10 | Papin | 1 |
| Bordeaux | 10 | Dugarry | 2 |
Here is my sample data, and this is what i want to get :
My dataset had budget values on each rows. I'd like to deal with those duplicate with such an expression : sum(distinct(Fields!Budget.Value)) but it seems RB can't perform multiple agregations... 😞
One solution could be to create an agregated dataset but i wonder if i will be able to connect/join my 2 datasets together in order to display both values in the same array. I think i won't be able to.
I grouped [Club] as a parent and [Budget] as a child, so the structure of my array seems to be pretty ok. But my sums are still displaying agregated values.
I struggle on expressions trying to find the good one. Any tips ?
Thanks you all
Hi slap33 ,
Follow the steps below to create a table or matrix visual in your reporting tool:
1.  Create a New Report
Click on "New Report" to begin the report creation process.
2.  Select the Visualization Type
Choose the "Table/Matrix Visual Wizard" to proceed with the structured data representation.
3.  Choose or Create a Dataset
Select an existing dataset or click on "Create Dataset" (located at the bottom left).
  Click "Next" to proceed.
4.  Establish a Data Connection
  Click on "New" to create a new data connection.
  Select the desired connection type:
In this example, the "Enter Data" option is selected. Alternatively, you may choose "Microsoft SQL Server" or another preferred data source.
Click "OK" and then "Next" to continue.
5.  Enter Data
  Input the required data manually (if using the "Enter Data" option). Click "Next" to proceed.
6.  Configure Row Groups and Values .In the Row Group section, select:
Club
Budget
In the Values section, assign:
Joueur
Salaire
7.  Adjust Layout Settings
Select the option "Blocked, Subtotal Below" for proper data structuring. Click "Next" to proceed.
8.  Finalize and Execute the Report . Click "Finish" to complete the setup.
Run the report to view the final output.
Hello slap33 ,
Apologies for the delay in getting back to you.
It looks like the issue you're facing is very similar to one that another user encountered earlier. That issue has been resolved, and the solution might be helpful in your case as well.
Please refer to the post linked below for the full explanation and steps:
Solved: Report Builder Nested SUM ( IIF Question - Microsoft Fabric Community
How do I calculate subtotals in paginated reports based on certain values? - Microsoft Q&A
If these don’t help, I recommend raising a support ticket with Microsoft for further assistance.Below is the link to create Microsoft support ticket:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticketIf this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
10 Replies
Hi slap33 ,
Thank you for reaching out to the Microsoft Fabric Community. We attempted using the standard 'enter data' dataset, and the output is shown in the screenshot below. Could you please confirm your data source, such as SQL or Excel? Additionally, providing your expected output would help us resolve the issue more efficiently. We look forward to your response.
FYI:
Thanks..
wow this is exactly what i want
for my sample data, i built it in an excel sheet yes
but my real life usecase is a sql source indeed, kinda same issue though (group / sum no duplicate)
how did you perform the trick ?
hope i dont bother you with my issue. Could you tell me what you did to get that wonderful output pls ? 🙂
thanks
Hi slap33 ,
Follow the steps below to create a table or matrix visual in your reporting tool:
1.  Create a New Report
Click on "New Report" to begin the report creation process.
2.  Select the Visualization Type
Choose the "Table/Matrix Visual Wizard" to proceed with the structured data representation.
3.  Choose or Create a Dataset
Select an existing dataset or click on "Create Dataset" (located at the bottom left).
  Click "Next" to proceed.
4.  Establish a Data Connection
  Click on "New" to create a new data connection.
  Select the desired connection type:
In this example, the "Enter Data" option is selected. Alternatively, you may choose "Microsoft SQL Server" or another preferred data source.
Click "OK" and then "Next" to continue.
5.  Enter Data
  Input the required data manually (if using the "Enter Data" option). Click "Next" to proceed.
6.  Configure Row Groups and Values .In the Row Group section, select:
Club
Budget
In the Values section, assign:
Joueur
Salaire
7.  Adjust Layout Settings
Select the option "Blocked, Subtotal Below" for proper data structuring. Click "Next" to proceed.
8.  Finalize and Execute the Report . Click "Finish" to complete the setup.
Run the report to view the final output.
Thank you V-yubandi-msft i look at your soluce and KYT
I did find a solution on my own with a subreport integrated in a main report. But i test your soluce right now and i tell you what.
Hi slap33 ,
Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.
One important thing is missing and that's what i'm looking for : the grand total of 110.
As the field is set in the Row Group section, i guess it is considered as an attribute and it can't be sum ?
Like in a pivottable, i guess the budget i need to sum needs to be in the detail values part. And there i gonna have my duplicates back again.
I'm afraid the only to do the job is to create a subreport (agregate) to import in the main detailed report.
Hello slap33 ,
Apologies for the delay in getting back to you.
It looks like the issue you're facing is very similar to one that another user encountered earlier. That issue has been resolved, and the solution might be helpful in your case as well.
Please refer to the post linked below for the full explanation and steps:
Solved: Report Builder Nested SUM ( IIF Question - Microsoft Fabric Community
How do I calculate subtotals in paginated reports based on certain values? - Microsoft Q&A
If these don’t help, I recommend raising a support ticket with Microsoft for further assistance.Below is the link to create Microsoft support ticket:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticketIf this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi slap33 ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,
B Manikanteswara Reddy
Hi slap33 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy
