Pivot tables often store data in the cache, and some of that data is also that about slicer elements. Slicers take values from the source table. If we add some of them and then delete them, it may happen that the slicers show phantom values. They are shown in pale color, as if there is no data. And indeed, if we pick such a value, we will get an empty pivot table report. How can slicer elements always be up to date?
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.
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!
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?