Aggregation in Power BI Report Builder (Paginated report)

[KPRL]

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. 

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.

Could anyone please guide me ?

Thanks,

KP

[Shahfaisal]

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

[KPRL]

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 :

Output that I am getting is :

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

Output should be like:

Can you please help mw with this ?

Thanks,

KP

[Shahfaisal]

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.

   

[KPRL]

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 :

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

How can I avaoid this issue?

Thanks,

KP

[Shahfaisal · 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...

[KPRL]

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