Applying filter / slicer to report table

hi clueless2024 you will need to create a measure that calculates the sum for sales, profits etc. For margins, since it's a percentage, average would be a better metric but do the same - create a measure calculating average of margins.

Once done, select a table visual and add the columns - Manager, your calculated measures to - "Add rows" in the "Build" pane. Next create a slicer with your months. Now regardless of whether you select one or multiple months, you will only see one line per manager for the totals of sales, profits, and the averages for the various margins for the selected months.

Below is an example from a sample dataset. The difference is instead of managers we have countries. Right click on your dataset and select "New Measure"

Thanks for the reply from pborah , please allow me to provide another insight:

Hi, clueless2024 

The reason you may not be aggregating by manager could be due to the presence of non-aggregated fields (columns) in your visual object. This issue might stem from the aggregation settings or the relationships between tables.

For example, when I modified the aggregation mode of the sales column in my table to "do not aggregate," the results when applying multiple selections in the slicer were as follows:

However, the results for a single selection in the slicer were normal:

My solution is to check which columns, apart from the manager column, are non-aggregated fields and apply aggregation to them.

This is currently the most effective solution. Of course, the method of aggregation may also depend on the data types involved.

For further details, please refer to the documentation.

Work with aggregates (sum, average, and so on) in Power BI - Power BI | Microsoft Learn

Data types in Power BI Desktop - Power BI | Microsoft Learn
 

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

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