Move the formula to a more centered location, change the font, and hide the row that contains the report filter:
Yeah, you probably see where this is going already: now you can reference the report filter cell in a formula, like this: 3) Use the Report Filter Cell in a Formula And unlike the performance penalty that can pile up with multiple slicers, duplicating a field like this will NOT make your pivot slower at all. Look what happens to the report filter in the sheet:Ĭool huh? Since they are the same field, the report filter has to always be in synch with the slicer. Here is the simplest method we have discovered so far: 1) Duplicate the field as a slicer AND a report filterįirst step is to take the field you want to use as a slicer, and add it to your pivot both as a slicer, and as a report filter, as in this simple pivot:ĭate Field Dragged to Both Slicer and Report Filter 2) Observe that the Report Filter “Tracks” the Slicer Sometimes, you just want to grab a slicer’s selected value and use it in an Excel formula, right there in the sheet. That’s a very useful trick, one that we employ all the time at Pivotstream.īut sometimes, that is overkill. The new technique is much less hassle.īack in June, Kasper posted a trick which lets you detect a user’s selection in a slicer, and use that in a PowerPivot measure. ***UPDATE: An even better technique is now posted here.
0 kommentar(er)
