Power bi paginated reports matrix total of filtered row group

[syedshah01]

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

[syedshah01]

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

[v-yohua-msft]

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.

[v-yohua-msft]

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.