The end of the old and the beginning of the new year is often a reason to look back. I read this post a while ago and it seems that I could have clarified one of the things better. There are many different views on pivot tables, and as Nectar’s advertising slogan says, “It does matter” In this “recipe” we will deal with each of them, after which you can decide for yourself when to apply which view to get a suitable report.
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?
Parameters are variables or sets of values that are intended to influence the appearance of a query. They are mostly used for filtering, but if you know the “M” language you can also use them for other purposes. Since I will not (for now) write about how “M” statements are created, I will use a simple example where a single parameter will be created and we will use it to filter a query.