[REPORT BUILDER Help] Nested Subtotal

[slap33]

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

[V-yubandi-msft · Accepted solution]

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.

[v-bmanikante]

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

[v-bmanikante]

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

[V-yubandi-msft]

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.

[slap33]

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.

[v-bmanikante · Accepted solution]

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-ticket

If 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

[slap33]

Hi @V-yubandi-msft  

hope i dont bother you with my issue. Could you tell me what you did to get that wonderful output pls ? 🙂

thanks

[V-yubandi-msft · Accepted solution]

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.

[slap33]

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.

[V-yubandi-msft]

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..

[slap33]

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 ?