Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KPRL
Frequent Visitor

Aggregation in Power BI Report Builder (Paginated report)

Hello,

I am trying to create a paginated report where trying to show Region,State and Sales as per below screenshot. Every state can have multiple sales and I want to populate sum(sales) at lowest level. 

 

KPRL_3-1670599124493.png

I am using expression as =sum(Fields!Sales.Value) for sales column . However, Data doesnot aggregate at state level. It returns table as input file while running the report.

KPRL_2-1670599000254.png

Could anyone please guide me ?

 

Thanks,

KP

 

1 ACCEPTED SOLUTION


@KPRL wrote:

Hi @Shahfaisal ,

 

Sorry, I was not very clear with my issue in previous reply. I am not looking for Total or Subtotal Row.

I want to see only 200 against State1, 200 against State2, 400 against State3 and so on in a single row. Instead I am getting 200 in 2 rows against State1(I am expecting single row against State1). 

Aggregation is happening at state level but aggregated value is duplicating for each state. Paginated report

 


You will need to add a total for the group as explained in the article and then delete the detail row. The detail row shows you data "as is" without any aggregation but the group will aggregate the data.

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/add-or-delete-a-group-in-a-da...

 

View solution in original post

6 REPLIES 6
Shahfaisal
Solution Sage
Solution Sage

You will need to create a group to be able to aggregate data. See Adding Grouping and Totals 

Hi @Shahfaisal ,

 

Thanks you.

I tried to create a group and aggregated the Sales. Added State(Sate- sorry for typo in the first post)  as  scope. However, output is coming something like below screenshot where sales value is repeating for each Id (ID column added letter).

So my current Input data looks like :

KPRL_0-1670863769151.png

Output that I am getting is :

KPRL_1-1670863808946.png

 

So, for each State I am getting 2 duplicate sales values instead of one value.

Output should be like:

KPRL_2-1670863956860.png

 

Can you please help mw with this ?

 

Thanks,

KP

 

From the screen shpt, it looks like you are missing a group sub-total. Please go through the article I shared in my previous response and look at the totals section.

Adding totals to a report

  1. Switch to the Design view.

  2. Right-click the data region cell that contains the [LineTotal] expression, and select Add Total. Report Designer adds a row with a sum of the dollar amount for each order.

  3. Right-click the cell that contains the field [Qty], and select Add Total. Report Designer adds a sum of the quantity for each order to the totals row.

  4. In the empty cell to the left of the Sum[Qty] cell, type the string "Order Total".

  5. You can add a background color to the totals row. Select the two sum cells and the label cell.

  6. From the Format menu, select Background Color > Light Gray square.

  7. Select OK to apply the format.

     
     

    Untitled.png

Hi @Shahfaisal ,

 

Sorry, I was not very clear with my issue in previous reply. I am not looking for Total or Subtotal Row.

I want to see only 200 against State1, 200 against State2, 400 against State3 and so on in a single row. Instead I am getting 200 in 2 rows against State1(I am expecting single row against State1). 

Aggregation is happening at state level but aggregated value is duplicating for each state. Paginated report output :

KPRL_0-1670924243638.png

I think this is happening becasue of 2 different ids against each states in the datasource.

KPRL_1-1670924381332.png

How can I avaoid this issue?

 

Thanks,

KP


@KPRL wrote:

Hi @Shahfaisal ,

 

Sorry, I was not very clear with my issue in previous reply. I am not looking for Total or Subtotal Row.

I want to see only 200 against State1, 200 against State2, 400 against State3 and so on in a single row. Instead I am getting 200 in 2 rows against State1(I am expecting single row against State1). 

Aggregation is happening at state level but aggregated value is duplicating for each state. Paginated report

 


You will need to add a total for the group as explained in the article and then delete the detail row. The detail row shows you data "as is" without any aggregation but the group will aggregate the data.

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/add-or-delete-a-group-in-a-da...

 

Thanks @Shahfaisal  for patiently replying to my queries. Deleting detail row did the trick and i was able to acheive what i looking for. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.