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
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:
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:
(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):
Change the Values Filter to Be >= 0
And that’s it!
The Readout
I also added two “readout” formulas:
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.”
