When we add dimensions to the Pivot table, columns in the report are filled in sequentially. If we use a different report layout than the Compact View, each report dimension will occupy one column, and then all columns in which the data aggregation is performed will be displayed. There is one setting within the pivot table options that allows us to differentiate values (the data we aggregate) from reporting dimensions.
If you run the Pivot table options (Analyze/Options), in the Totals & Filters section, you will find the Allow multiple filters per field setting. It allows you to apply text filters and number filters simultaneously for the reporting dimension, which can be displayed in rows or columns. This feature could be useful, let’s see how it works!
Include Filtered Items in Totals is a very useful option that we can find in pivot table settings, and it allows us to display the correct total for values in rows or columns that we have previously filtered. By using the DAX expressions we can most certainly reset the filters and thus calculate the correct sums, but sometimes there is no need to do this. Only by selecting/deselecting this simple option, we can get the correct totals in the report.
In one of the previous posts, there was a general mention of pivot table options, and in the next few “recipes” we will address some of them that are important and can make our daily work easier. Filtering the pivot table is achieved by dragging report dimensions into the Filters field. How can we influence the number and layout of available filters in the pivot table?