Adding a Minimum Threshold Slicer to “Stores That went negative” Technique

 
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

6 Responses to Adding a Minimum Threshold Slicer to “Stores That went negative” Technique

  1. Wayne Ivory says:

    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??

    • powerpivotpro says:

      Wayne – sorry for the slow reply. I will need more detail before I can answer your question, and maybe even then it will be a mystery to me :)

      If you put your version of [SameStoreSales] on a pivot, do you indeed see blanks? And the IF() test in the [Negative Growth Stores] measure fails to detect those?

      • Wayne Ivory says:

        Hi Rob. Just wanted to get back and say I figured out that my problem was occurring because the [SameStoreSales]-equivalent value I had in the COUNTX formula was a table field (rather than a measure) which I hadn’t aggregated. Studying and studying your sample workbook got me through.

        28 years I’ve been programming, at least 10 years of which I’ve been doing non-trivial Excel stuff, and here I find myself with my Learner-driver sign strapped on and brightly showing! Fortunately exciting new tools like PowerPivot make me happy to be there.

  2. chuckish says:

    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?

    • powerpivotpro says:

      Wow, I just tried it in 2013 and got the same results. I will forward the workbook to MS and ask them “wasup with dat” or something along those lines :)

      • chuckish says:

        In a way I’m relieved and not some rogue add-in getting in the way. I’ll keep checking back to see if you’ve made any progress with MS.

Leave a Comment or Question