The Pivot Pictured Above Acts as if We’ve Swapped Out Fields on Rows – in Response to a Slicer Click! First off…my first post! Being one of the newest (and youngest) members of the PowerPivotPro family has been very exciting…
Guest Post by Andrew Todd
Cube Formulas: Ultimate flexibility for your reports but lots of drama to update…
A while back, I was tasked with finding a way to automate upkeep of a scorecard built primarily with thousands of cube formulas (Yes, thousands!). This particular scorecard was still under development, and maintaining and making changes to it had become a full-time job! All of the individual cube formulas needed to be updated several times a week, and this was expected to go on for months as executives made up their minds on the final product.
Fortunately, I found two tricks that allowed me to:
a.) Change slicer references in all cube formulas with a single click
b.) Modify entire tables across multiple sheets in seconds
These two tricks freed up time that I used to drive further improvements and start performing real analysis, rather than just maintaining a report.
Cube Formulas: Flexible and Powerful
Cube formulas allow you to add PowerPivot/SSAS Tabular calculations to any cell in virtually any orientation that you can think of. They’re a big part of what makes Excel simply the world’s best data tool, period. (Imagine if you could use them in Power BI Designer!)
The flexibility of cube formulas is powerful, but it does carry a price. Cube formulas are worksheet functions, so they bring their ‘worksheet function drama’ with them (lack of portability, unique formulas, individual updates, etc).
Here are two tricks to keep your formulas easy to maintain and update!
Cube Formula Trick 1: Consolidate Your Slicer References!
The first thing I noticed when opening my scorecard was the length of the cube formulas! I clicked on a cell containing a CUBEVALUE() formula and was greeted by multiple rows of slicer names in the function bar. The scorecard had a total of 10 slicers, and each needed to be referenced in the formulas. What’s more, not all of the slicer names in the scorecard were to be included in the calculations and yet more slicers were set to be added in the future!
Yikes! Imagine updating thousands of these! I’m going cross-eyed already!
The D-Man Innovates!
Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas. While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated. I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user. There is a link to this workbook at the end of this post.
The techniques I have used are:
- Disconnected slicers used to create interactive chart series
- Cube formulae and standard Excel to make an interactive chart title
I love these 2 tips I learnt from Rob – so user friendly. However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.
- Cube formulae and standard Excel to make an interactive legend
- VBA and “link to source” for interactive axis formatting
- Excel VBA to change which Axis the series appears on.
I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below. I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.
Now let me call out the key techniques I have used to make this workbook rock.
That’s a Single Spreadsheet Cell with a CUBEVALUE Formula AND a Sparkline in It!
I was working with a client last week when a question occurred to me:
“Can I put a Sparkline in a cell that already has a Cube Formula in it?”
”Oh cool, it worked!” (Cackles Maniacally)
-me, last week
Anyway, we were off and running at that point:
“FrankenSpark” Used as Part of a Larger (Redacted and Obfuscated) Client Scorecard
(Yes, the Colors Still Need Some Work)
Back from Paradise, Here’s a Quick Tip
Funny thing about vacations is that everyone is waiting on you when you get home. But man, what a vacation.
Anyway, I’m juggling my final edits to Alchemy, spending two full days with a client, AND teaching a class on Wednesday/Thursday, so today’s post will be brief, but hopefully still useful:
PivotTable with Two Slicers. Gender Slicer “Cross Filters” the Customer Name Slicer,
As Expected. All is Right with the World.
Now we convert the pivot to cube formulas:
And look what happens to the cross filtering:
Custom “On Hover” Tooltips on Each Cell in the Dashboard!
(The yellow dot and distortion around mouse pointer are GIF side effects and do NOT appear in Excel)
Question from PowerPivotPro School!
Got a great question the other day from Oscar, a student in PowerPivotPro School…
[OSCAR]: “Is it possible to have a tooltip in powerpivot which shows additional information based on the cells selected (or mouse roll over). the info to be displayed comes in from a table created with cube fuctions on the same data source. So the coordinates of the highlighted cells would be inputs for the cube formula and result displayed in a tool tip dynamically.”
My first thought was “no, not possible.” Then ten seconds later, a guerilla-style hack came to mind. And then, my reply:
[ROB]: “Oscar you are a very, VERY bad man. I am now obsessed with this problem. There goes my Sunday.”
The Trick: Hyperlinks to Nowhere!
The One and Only “Sam Rad”
At 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.
BTW, if you want to read that post Ken was tweeting about (the one written by Bob Phillips, another Excel MVP), here it is: Click to Read Bob Phillips, Excel MVP, Writing About Cube Formulas in PowerPivot
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:
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:
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.
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.
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
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:
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.