Search code examples
reporting-servicespowerbi

Power BI Paginated Report

I am working on Power BI paginated report and I need add row totals based on the values in column which is coming from SQL.

Below is the desired output I need. enter image description here From my SQL query I am pulling data for all categories as attached in image like 15.a .w1 b.w2, c.w3,d.w4. Similarly for Inf1 inf2 inf3 for each month wise.

Query Result: enter image description here

and my report design: enter image description here

In the report I need to add row total after w1 as e.Total w (15.a.w1+b.w2+c.W3) for each month as mentioned in image. Same for Inf as well, need add row total for d.Total Inf(inf1+inf2+inf3).(https://i.sstatic.net/5jTkF.png)

I have tried to add row total in it was adding all the rows not category wise.

Thanks

Solution

  • You need to create a row group that groups by whatever you need. In your sample data it looks like they are grouped by the first number in the subcategory.

    You could either add this to your dataset query, to do it in the report itself.

    As I don't know what options you have, I'll go down the report expression route.

    Add a parent group to the detail row group, you can also select to add a footer at this point.

    For the "group by" expression use the following..

    =LEFT(Fields!SubCategory.Value, INSTR(Fields!SubCategory.Value, ".")-1)

    This will return anything before the first . (e.g. 15).

    The above expression should have automatically been applied to the sort for the row group to.

    You can remove the first column if required but don't remove the group if prompted.

    Then simply select the fields for the total row as normal.

    Here's a short gif (40 seconds) to show you how to do it (click it and maximise the browser if required).

    enter image description here