topic Re: Power bi paginated reports matrix total of filtered row group in Report Server

Power bi paginated reports matrix total of filtered row group

syedshah01 — Tue, 25 Jun 2024 14:32:59 GMT

Hi,

I have created a matrix in the paginated report. I created a row group in the matrix where I am showing the sales (sum(sale_amount) for only those products who sold atleast 100 quantities or more in the current month. The filter on the group is count(items) >= 100. This works fine and gives the desired result.

Now we wanted to add a total to this group to see the total sale amount. The problem with the total is that it shows the total sales of everything that is sold in the current month. I want the total to only total the sale of the group where the filter is count(items) >= 100.

Can someone please give me a solution to this.

Regards

Re: Power bi paginated reports matrix total of filtered row group

Anonymous — Wed, 26 Jun 2024 02:21:00 GMT

Hi, syedshah01

I create sample data

Open your report and select the matrix to which you want to add a row group.
In the Report Designer, in the Row Group area, right-click and select Add Group.

In the dialog box that pops up, select the field you want to group (in our case, this would be the "Product ID").
Click OK to add the row group.

Right-click on the row group and select Group Properties. In the Filters tab, you can add a new filter with count(items) > = 100.

Add a total to see the total sales of products that meet the filter criteria. Right-click on the row group and select Add Grand Totals:

=Sum(IIF(Fields!SalesVolume.Value >= 100, Fields!SalesAmount.Value, 0))

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Power bi paginated reports matrix total of filtered row group

syedshah01 — Sat, 29 Jun 2024 11:58:09 GMT

how would i create a grand total expression that only takes the top 5 n into account? If the filter was to only take the top 5

Re: Power bi paginated reports matrix total of filtered row group

Anonymous — Mon, 01 Jul 2024 02:54:04 GMT

Hi, syedshah01

You can right click Datasets and select Get data, you can create index in powerquery

Then use the new filter to filter the Top5

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.