Thursday’s Post “Fixed” The Number of Negative Stores for a Month at 8.  Now We Vary That Threshold That With a Slicer.  PowerPivot is Amazing :)

Thursday’s Post “Fixed” The Number of Negative Stores for a Month at 8.
Now We Vary That Threshold That With a Slicer.

Let’s take Thursday’s post and extend it a bit.

In the picture above you’ll see that I have 5 selected as my threshold on the new slicer, and 48 months “qualify” for that threshold – there are 48 months where at least 5 stores were negative.

Now let me select 9 on the threshold slicer:

Raising the Threshold to 9 Weeds Out 10 More Months, Only 38 Months Exhibited 9+ Negative Stores.  Did i mention that PowerPivot Rocks? :)

Raising the Threshold to 9 Weeds Out 10 More Months, Only 38 Months Exhibited 9+ Negative Stores

How’d I Do This?

With one of my favorite “go to” techniques:  the disconnected slicer.

I created a table in Excel with the numbers 1-10 and then pasted that into PowerPivot:

image

The Stores Negative Table:  Pasted From Excel,
and NOT Related to Any Other Tables in my Workbook

Then I create a “harvester” measure off of it:

image

(By the way, this simple and crazy-powerful technique is explained from scratch in the book, and also used many times on the blog – search for “disconnected slicers” and you’ll find multiple posts.)

Then I modify my original [Negative Growth Stores Measure] by simply subtracting the new [min Negative Stores] measure from the original formula:

[Negative Growth Stores Measure] =
   COUNTX(VALUES(Stores[StoreID]),
          IF([SameStoreSales]<0,1,BLANK())
         ) – [Min Negative Stores]

This formula, then, will return 0 when I have exactly the number of negative stores that my threshold slicer specifies, and greater than zero when there are more negative stores than the threshold.

So now I just modify my Values Filter to be Greater than or Equal to 0 (rather than 8, as it was before):

image

Change the Values Filter to Be >= 0

And that’s it!

The Readout

I also added two “readout” formulas:

image

image

Here’s that second formula as text in case you’d like to copy it:

=”Months have at least ” & CUBEVALUE(“PowerPivot Data”,”[Measures].[Min Negative Stores]”,Slicer_Stores_Negative) & ” stores that went negative.”

Download the Workbook!

Click here to download this workbook

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 6 Comments

  1. This post and its predecessor are very interesting. The situation is similar to a question I faced recently except (in equivalent terms) I wanted to count the number of Stores with *no* sales (i.e. the white cells in your PivotTable with no underlying source records) for each month. I ended up having to contruct a complex workaround back in the source data but your method looks so much simpler.

    However when I try to replicate your (original) Negative Growth Stores measure, neither testing for [SameStoreSales]=0 nor ISBLANK([SameStoreSales]) yields a result. Perhaps a different approach is required when there is no source data??

  2. Good stuff, as usual. I’m curious if you’ve run into the issue that I am when using your downloadable workbook: Excel barks out a message when either trying to click on a Slicer or Manage PowerPivot that the data model needs to be upgraded to Excel 2013. Excel further tells me that PowerPivot is unable to load the Data Model. Google/Bing searches have yielded no practical answers. Any thoughts?

Leave a Comment or Question