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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
syedshah01
Regular Visitor

Power bi paginated reports matrix total of filtered row group

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

3 REPLIES 3
syedshah01
Regular Visitor

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

Anonymous
Not applicable

Hi, @syedshah01 

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

 

vyohuamsft_0-1719802252602.png

vyohuamsft_1-1719802423362.png

vyohuamsft_2-1719802433191.png

 

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.

 

Anonymous
Not applicable

Hi, @syedshah01 

I create sample data

vyohuamsft_0-1719367880804.png

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.

vyohuamsft_1-1719368174609.png

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.

vyohuamsft_2-1719368254083.png

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))

vyohuamsft_4-1719368377113.png

Here is my preview:

vyohuamsft_5-1719368412627.png

 

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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