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

April 30, 2013

 
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?

Read the rest of this entry »

6 Comments | Accounting/Financial Techniques, Creative Visualizations, DAX, Quick Tips & Tricks, Slicers | Permalink
Posted by powerpivotpro

Guest Post: Always Show Yesterday, Today, or Tomorrow’s Data

March 28, 2013

 
Today we have a new guest poster – Miguel Escobar.  I’ve been talking to Miguel in email and Skype for a long time now and feel silly that I haven’t asked him to do a guest post until today.  But now, I have, so I can stop feeling silly.

From his writing style and creative approach to solving problems, I think you’ll see that he fits right in.

Cool trick: Always show Yesterday’s, Today’s or Tomorrow’s Data

clip_image002

Executive: Are these values correct?
Excel-guy: yes, but you need to check the dates slicers to see what dates the report is using
Executive: Ugh… I just want to click on the report and see the latest values

If you ever had this situation before let me tell you that you’re not alone on that one…I’ve been there before and it’s time to give you some cool easy tricks on how to set up a Powerpivot report that shows you the yesterday, todays, tomorrow, next week or any type of timeframe (forecasting or that sort of scenario).

Read the rest of this entry »

14 Comments | Custom Calendars, DAX, Quick Tips & Tricks, Slicers | Permalink
Posted by Miguel Escobar

Toggling Conditional Formatting On/Off via Slicer

February 28, 2013

 
PowerPivot Makes it Easy to Toggle Conditional Formatting On and Off via Slicers

The One and Only “Sam Rad”

imageAt the Microsoft MVP Summit last week in Seattle/Redmond, I got to see and hear a lot of cool things.  But none of that compares to seeing old friends and colleagues – some of the most extraordinary people I have ever known (and ever WILL know).

People like Sam Radakovitz for instance, aka “SamRad.”  A veteran Excel team member who briefly left to do other things but is now back on Excel.  This is Very Good News.

(Actually this is a theme – a lot of former Excel program managers in particular have “come home” from other places and have rejoined the team.  I expect this next release of Excel to be something special).

In addition to being a numbers/technical guy, Sam is very visually gifted.  He takes bland stuff and makes it sing.

We all could learn a thing or two (or a hundred) from Sam.  Even in spreadsheets, presentation quality has tremendous impact.

Read the rest of this entry »

13 Comments | Creative Visualizations, Cube Formulas, Quick Tips & Tricks, Slicers | Permalink
Posted by powerpivotpro

Stars and Snowflakes and Bears, Oh My!

January 31, 2013

 
Stars and Snowflakes in PowerPivot. Snowflakes are slow and evil.

A PowerPivot Model with “Single-Step” Relationships is a “Star,” and one with “Daisy-Chained” Relationships is a “Snowflake.”  Snowflakes are Often Slow.  And Evil.  Don’t trust them.
(Images taken from page 194 of the book).

“Hey, Why Did Things Get So Slow?”

Got a great question this week from Dave Boylan:

“I think I read in your book that you should always use dimension (lookup table) data in your slicers and page fields (even if the same data is in your fact/data table) because they behave better and perhaps use less memory.  This makes sense as my fact table has 2MM rows and the slicer has three distinct values.”

Yes – if you have a lookup table, you should use fields from that lookup table on your pivot, rather than their equivalent fields from your data table.  My recent post on using fields from your date table on the pivot is just one example of this.

Read the rest of this entry »

11 Comments | Performance, Quick Tips & Tricks, Slicers | Permalink
Posted by powerpivotpro

Modeling Viral and Marketing Growth, Part 3 of 3

January 24, 2013

Why am I doing this in PowerPivot?  Primarily as a challenge.

This is a question I should have answered before I even started down this road.

To be honest, I did it primarily as a challenge – to stretch my brain a little bit.  If I were faced with this exact same task in my daily work, undoubtedly I would just use normal Excel formulas.  In some ways, this modeling exercise has been a deliberate misuse of PowerPivot.  A handful of parameters with no source data whatsoever – this is NOT what the PowerPivot engine was built for, which explains why the PowerPivot solution is actually significantly more difficult than the Excel solution.

“So you’ve been deliberately wasting our time??”

No, I do think there is real value in this exercise, for two reasons:

  1. Brain-stretching with new techniques always comes in handy later.  For instance, on the first post Sergey commented that he’d been thinking about loan amortization measures and this could be applied to that.
  2. I can see this technique being added, as a supplement, to a broader PowerPivot model.  For instance, a model containing lots of real customer data over time, and then a [Projected Customers] measure that forecasts future customer populations based on various assumptions and/or marketing investments.

So with that in mind, here it is:  the final installment of viral/marketing modeling in PowerPivot.

Read the rest of this entry »

Leave a Comment » | Accounting/Financial Techniques, CRM, Custom Calendars, DAX, Marketing Analysis, Quick Tips & Tricks, Slicers | Permalink
Posted by powerpivotpro

Modeling Viral Growth and Marketing in PowerPivot

January 18, 2013

A Tale of Two Charts

Let’s say you operate a business that relies heavily on “word of mouth” – customers recommending your product/service to their friends and colleagues. Or at least, you THINK it relies heavily on that sort of thing.

You need to decide how much to spend on traditional advertising – to supplement the social/viral marketing that your customers do on your behalf.  Take a look at each of these two charts – the captions for each attempt to capture the knee-jerk conclusions you might draw:

 
Modeling Viral Growth versus Traditional Direct Advertising in PowerPivot

“Advertising?  We Don’t Need No Stinking Advertising!
That is SO Yesterday!  We’re Viral Baby!”

Modeling Viral Growth versus Traditional Direct Advertising in PowerPivot

“All These Youngsters and Their ‘Viral This’ and ‘Social Media That’ – That’s All Just Fancy Excuses to Be Lazy – You Clearly Need to BRING Your Message to the Customer”

If chart 1 reflected reality, you may opt to spend very little on traditional advertising.  But in a chart 2 world, you’d be silly to rely on viral growth.  But which one (if either of them) describes your situation?

Back in October, Rahul Vohra (CEO of Rapportive) wrote a two-part blog series on this topic, posted here on LinkedIn.  I took a note, at the time, to revisit his work and “convert” it to PowerPivot.

It’s a very different kind of problem from what I normally do in PowerPivot – this isn’t about analyzing data I already have, but about calculating future outcomes based on a handful of parameters.  And that leads to some different kinds of thinking, as you will see.

 

Read the rest of this entry »

1 Comment | Accounting/Financial Techniques, DAX, Marketing Analysis, Medical/Scientific, Quick Tips & Tricks, Slicers | Permalink
Posted by powerpivotpro

“Initializing” Slicers with too Many Values to Scroll

December 11, 2012

 
image

Has THIS ever happened to you?  We’re not even out of the Aarons yet.

Simple and Slick

Yesterday I saw Ken Puls post a really simple and effective trick – intentionally introducing a BLANK() measure in your pivot just to add a spacer column or row – and my response was “simple and slick, I like it.”

Well today I’m in Michigan, conducting some PowerPivot training/consulting, and that makes today a great day for a similarly slick and simple trick.  Because hey, I’m not even here right now.  Who’s writing this post?? Smile

First Initial!

Over in the Customers table in the PowerPivot window, add a simple calc column:

Read the rest of this entry »

1 Comment | Creative Visualizations, Slicers, Usability and Appearance | Permalink
Posted by powerpivotpro

One Slicer to Control Them All…

November 27, 2012

 

One Slicer That "Controls" Other Slicers. Multiple slicers "get set" when the user makes one click.

Got this question yesterday from the DJ Monkey…

Let’s say you have three slicers:  Percentile, Quarter, and Category:

image

For Bikes, You Always Want to See the 70th Pctile in Q2

And you find yourself selecting the same combinations over and over again.  Above, you selected 70, 2, and Bikes – because Quarter 2 is essential for Bike sales, and 70 is the percentile you are most interested in for bikes.  (Note that this percentile drives conditional formatting in this example – see this post for details).

When you look at Accessories, you always want to see Q1 instead, and set the Percentile threshold to 60.  And for Clothing, it’s a different set as well:

image

But for Accessories, You Always Want to See the 60th Pctile in Q1

image

And for Clothing, It’s 80 and Q4 That You Always Want to See

Is that too repetitive for you?  Three clicks with a brief pause in between each makes you feel like a monkey?  There’s no satisfying you, is there? Sheesh Smile

But hey, there’s a way to essentially set multiple slicers in one click, without VBA, as long as you know ahead of time what the desired combinations are.

Read the rest of this entry »

Leave a Comment » | Creative Visualizations, DAX, Quick Tips & Tricks, Slicers | Permalink
Posted by powerpivotpro

David Hager on Dynamic Conditional Formatting

November 13, 2012

Intro From Rob:  Greetings from Vegas!

Well ok, Vegas isn’t tons of fun when you don’t leave your hotel room very much, but hey, I have a great view of castles and downtown Manhattan.  I’m a little worried that this photo may open a wormhole into some alternate universe however:

image

Hey Wait a Second – How Is this Picture ALREADY in
the Blog Post Being Written in The Same Picture???  My Head Hurts.

Anyway, today David Hager has graciously stepped to the plate with a guest post.  He’s seen me goofing around with conditional formatting a lot and he’s got some advanced techniques to add to the mix.  Today’s post sets the stage for that.

It all revolves around the capability of CF (Conditional Formatting) to use formulas as the “decider” of whether or not to format a cell:

image

This CF Rule Type is Important to David’s Techniques

All right, take it away Mr. Hager…

CHANGING CONDITIONAL FORMATTING LOGIC USING DROPDOWNS

By David Hager

There has been a lot of demos and discussion on this blog relating to the innovative use of conditional formatting (CF), particularly in connection with PowerPivot.

http://www.powerpivotpro.com/?s=%22conditional+formatting%22

The technique of CF in Excel is especially powerful where formulas are used to define the desired TRUE/FALSE condition to be applied. It occurred to me that in this era of data visualization and controls (such as slicers) for altering the data view, there was no user method for changing the underlying logic of applied conditional formats. As an example, say that on a worksheet that cell D7 has a CF formula of =D7>8. There is no way to change the CF to =L7>8 unless the CF control on the Ribbon is opened and the formula is manually changed. Further, a CF formula with both conditions ( =AND(D7>8,L7>8) ) may be what is desired, and the user may not know how to do this. Thus, I decided to create a method that allows for the selection and changing of CF conditions from dropdowns using data validation lists.

Read the rest of this entry »

1 Comment | Creative Visualizations, Cube Formulas, Slicers | Permalink
Posted by powerpivotpro

Better Way to “Catch” Multiple Slicer Selections in a Formula

November 8, 2012

 
It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas, IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas, IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas,
IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

A Popular Topic

No, I don’t mean things like “Fox Urine” or “Face to Anogenitaled” – those are pretty funny of course, and they come up in my job because I consult for my scientist neighbor on his lab rat projects.

But no, I’m here to talk about something even more popular than Fox Urine Smile

Every day, one of the most-read topics on this blog is some variant of “I want to catch slicer selections in formulas.”  This has been covered in at least three different posts:

  1. http://www.powerpivotpro.com/2010/06/use-slicer-values-in-a-calculation-with-powerpivot-dax/
  2. http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/ 
  3. http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/

There have also been a number of comments on each post that suggested alternate (and often better) ways of doing things.  I’ve never been terribly comfortable with that third post in particular, the one dealing with multiple selections.

Funny thing is, I used a new technique (for me) in the Calendar Chart posts, but never went back and called it out explicitly.  Today I am going to correct that omission.

Read the rest of this entry »

8 Comments | Creative Visualizations, Cube Formulas, Quick Tips & Tricks, Slicers | Permalink
Posted by powerpivotpro

Conditional Formatting via Slicers, Part Two

October 4, 2012

 
Using PowerPivot Measures and Slicers to Control Conditional Formatting on the Pivot!

Each Number Refedit in the Edit Rule Dialog Points to a Different Cell on the Sheet

Correcting for that bug

In the last post on this topic, I discovered what I think is a bug in Excel 2010 conditional formatting, one that prevented me from using the Percentile threshold type with a cell reference. 

But the Number type works great, so if I can get the actual [Profit] value for, say, the 80th percentile Model Name into a cell, I can reference that.

And that’s precisely what those three cells in the image above contain:

image

Cell F2 Contains a Cube Formula that Returns the Measure [Profit Required for Green CF]

OK, so how do I calculate that [Profit Required for Green CF] measure?

Read the rest of this entry »

Leave a Comment » | Creative Visualizations, Quick Tips & Tricks, Slicers, Usability and Appearance | Permalink
Posted by powerpivotpro

Display User’s Slicer Selections: A Macro to Automatically Create the Formulas

October 2, 2012

 
Slicer Selections Displayed via Formulas

Note the “Readout” that Displays the User’s Slicer Selections:
Now Do This for Every Pivot in Your Workbook With One Click!

A Common Trick, Now Automated

This is something we do all the time at Pivotstream – we write formulas that capture user slicer selections and then display those selections back to them.

We do this via hidden report filters:

image

(Yes, we could also do this with cube formulas, but we started out (literally years ago now) using this approach and we’ve just kinda stayed with it.  I’m not sure cube formulas would be better, but they might be.)

Why is the Readout Useful?

Why do we do this?  Well, for one thing, the Download Snapshot feature on the server does NOT download the slicers – you get a big blank white space where the slicers were, which isn’t terribly helpful.  It leaves you wondering what you had selected.

It’s also useful when there are slicers on other sheets impacting your current sheet.  And even on a single sheet, it’s often nice to have a compact readout of your selections without having to scroll (or even scan with your eyes) to see what selections you have made.

Read the rest of this entry »

3 Comments | Quick Tips & Tricks, Slicers, Usability and Appearance, VBA / Macros | Permalink
Posted by powerpivotpro

« Previous Entries