In market slicer, where all markets were selected (when dashboard was sent out) the European markets automatically turn dark blue and other markets turn very pale blue (not grey) and the cuberankedmember still returns “All” value. However, lets say if I now select only a single region (Europe lets say), then in region slicer – Europe will become a darker blue color while other regions turn grey. When I do this, the cuberankedmember function for returning market names (selected in slicer) returns the “All” value which is fine & all good. When I send the dashboard out, I select all 4 regions (in region slicer) and all 20 unique markets (in market slicer) – to get the global view and send it to my audience. First one is a “Region” slicer (showing 4 regions – Europe, JAPA, LAC and US) and second slicer is a “Market” slicer (showing all unique markets belonging to the 4 regions). Hi – I have a unique problem with the cuberankedmember function. 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. In the dialog box (image below), select the field you want to insert a Slicer for: Note: if you already have a Slicer inserted, you can connect it to the quasi PivotTable by right-clicking the Slicer > Connections > check the box for the quasi PivotTable. 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 It’s dead easy with a cell selected in your PivotTable go to the Insert tab > 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)
