Custom Toooltips in Dashboards!

November 12, 2013

Power Pivot Dashboard Tooltips/Comments

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!

Read the rest of this entry »

Bubbling Up Exceptions with “Sara Problem?”

October 17, 2013

Power Pivot Allows Some Amazing Stuff, Like Exception Flags that "Bubble Up!"

The Flag Appears at Top Level (Accessories), Telling You There’s a Problem Further Down.
Expand Accessories and You See the Culprit is Bottles and Cages (Specifically Road Bottle Cage)

Start With a “Sara Problem” Measure!

Quick post today, continuing the saga of Anakin Skywalker.  An extension to the bubble up ranks technique.

Let’s say you have a measure.  Doesn’t matter what it is or how it’s calculated really, except that it reports on whether there’s a problem.  It returns 1 (or Yes or True) if there’s a problem, and 0 (or No/False) if there isn’t.  Or maybe it returns a “regular” number, but when it crosses a certain line, your business has decided that is Bad.

Get it?   The measure is named “Sara Problem!!!!”  To pronounce it properly you have to add a question mark – so technically, it’s named…  “Sara Problem?

Great pun!  But I can’t take credit for it.  Back when we lived in Seattle, my wife (girlfriend at the time, and fellow Microsoft engineer) played roller derby.  She was a “Rat City Rollergirl,”  she played for a team called Grave Danger, and her skater name was Natalie Fatality.

I am not making this up.  Well, she had a teammate whose skater name was Sara Problem.

This has nothing to do with Power Pivot, or at least not directly.  But c'mon, it's badass :)

“Sara Problem” on Left, “Natalie Fatality” (Official Wife of PowerPivotPro) on Right

Back to the Formulas!


Read the rest of this entry »

“Initializing” Slicers with too Many Values to Scroll

December 11, 2012


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 »

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:


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 »

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:


(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 »

Conditional Formatting Controlled via Slicers!

September 27, 2012

Yes folks, we’ve seen all manner of parameterization by slicer.  We’ve even seen sort by slicer.  But now it’s time for me to track down a hunch I’ve had for awhile now:  we can also control conditional formatting via slicers!

At the 65th Percentile, Model Name Profits are Being Shaded Green


Getting Stricter:  Use the Slicer to Set the “Green Threshold” to 95th Percentile

Read the rest of this entry »

Last Mini-Post for the Day: Check out Chandoo’s PIVOT version of the Calendar Chart!

September 13, 2012

I thought I had seen everything.  I had not.  Chandoo, we bow before you:

Yes, That’s a PivotTable!  Click the Image to View the Post on

(Oh, and I really need to figure out how to make these animated GIF’s.  Damn!)

PowerPivot Calendar Chart in Excel: Specific Steps for Adapting it to Work With YOUR Data

August 16, 2012


Modifying This to Work With Your Existing Workbook Isn’t Hard


Given the continued popularity of the Calendar Chart and the post I did on its anatomy, I thought I’d continue today with a more pragmatic “how do I adapt this to work with my data?” post.

Adding the Calendar Chart to YOUR PowerPivot Workbook

OK, so you like the calendar chart but you don’t want to start from scratch in a new workbook?  You already HAVE a PowerPivot workbook and want to just “port” the calendar chart into THAT workbook?

It’s easy.  Probably a 30 minute task, and that includes the time spent reading this post.

Read the rest of this entry »

Explaining the PowerPivot Calendar Chart, Plus an Updated XLSX Download

August 14, 2012


Hidden Rows and Columns Visible, Color Coded, and Explained
(Slicers Deliberately Moved Aside for Clarity)
(Click for Larger Version)

A Most Popular Post Indeed!

Well the CalChart post was a hit – the second most popular post of this year in fact.  (Second only to Dan Battagin’s spreadsheet formatting post, and that one had the benefit of being directly linked to from the official Excel blog – Dan is a big cheater).

I particularly enjoy how many Excel Pros are arriving at this blog for the first time as a result of the CalChart – you know who you are!  You’re helpless against the luxuriant charms of the CalChart! :)

And you have to have PowerPivot for it to work, muhaha.  Resistance is futile.  Go download it from Microsoft now.  It’s free.

Modifying it to fit your needs

The workbook I made available for download last week included a bunch of unused “machinery” – formulas and cells that I created while I was experimenting with different techniques, but ended up not using in the final version.

Read the rest of this entry »

Introducing… the Calendar Chart!

August 9, 2012

Calendar Chart in Excel - PowerPivot Can Do Some Amazing Things

“CalChart” – The Most Absolutely Awesome Thing I’ve Ever Done in Excel
(Data:  Fake UFO Sightings/Alien Abductions – Data I 100% Made Up)

New Chart Type Added to Excel 2010!

Yes, it’s a new chart type.  And yes, it’s been added to Excel 2010.  But not by my former colleagues at Microsoft.  This was done by me, after being inspired by another Excel pro, and with a heavy dose of formatting and sparkline assistance from another.

And it’s not some new fancy software addin or something like that.

It’s formulas.  In the normal Excel grid.

Read the rest of this entry »

Creating a Cycle Plot PivotChart

July 31, 2012

Guest Post by Colin Banfield [LinkedIn]

A cycle chart is a chart designed to show business sales cycles in a manner that cannot be expressed using other standard charts. For instance, a cycle charts can show how monthly sales vary over several years, or how daily sales vary over several weeks. For a good introduction to cycle charts, see this excellent article by Naomi Robbins.

Figure 1 shows cycle PivotCharts using 3M+ rows of sales data from the Contoso database.



Figure 1 – Monthly sales over years and daily sales over weeks cycle plots (click figure to see an expanded image)

Read the rest of this entry »

A Brief Note on PowerPivot and Custom PivotCharts

July 19, 2012

Guest Post by Colin Banfield [LinkedIn]

In the next few posts, I plan on demonstrating techniques for creating various types of custom PivotCharts that use PowerPivot data and DAX measures. Each custom chart will have one or more of the following characteristics:

  • Non-native to Excel, but nonetheless useful for various types of analysis. The box-and-whisker plot that I created in this post is one example of a non-native chart.
  • Dynamic in nature, allowing charts to change based on slicer selections. The dynamic charts that I created in this post is one such example.
  • Provide additional context, such as reference lines or conditional formats.

Why this emphasis on Custom PivotCharts?

A chart (really a graph, since a chart can be a table or graph. I’m using the term in Excel’s context.) is the best way to analyze data – for detecting patterns, trends, outliers, correlations, making comparisons, and so on.  A custom chart, done correctly, can facilitate the analysis process immensely. There are several Excel sites that are dedicated to the topic of custom charting. However, these sites are primarily focused on building charts from static data in Excel. Excel has not traditionally provided tools for easily interacting with charts created from data in worksheet cells or tables (and the available tools are limited at best). Furthermore, very few posts on the Excel sites discuss charting using PivotTable data. There’s no surprise here – standard Excel PivotTables are exceedingly limited in the way you can organize data for custom charting. For instance, you can’t modify or override row or column filters, and calculated fields are very limited in scope – so much so that I rarely use them.

However, PowerPivot and DAX ameliorate many of the standard PivotTable limitations – to the point that in many cases, I actually find it easier to organize PivotTable measures for custom charting, compared to organizing data in worksheet ranges or tables. In addition, PivotTables containing DAX measures in combination with appropriate slicers, provide a level of interactivity that is not otherwise possible.

It’s not all nirvana though – you cannot create scatterplot or bubble PivotCharts. Therefore, an important class of analysis dealing with detecting correlations in data, cannot be done in PivotTables (though such analysis, and more, can be done with the Power View add-in that ships with Excel 2013).

To date, chart enthusiasts have been severely underserved by the paucity of PowerPivot posts dedicated to the topic of custom charts. Therefore, I will attempt to redress the balance. These custom charts should be relevant for years to come. I was shocked, saddened, and disappointed to learn that no new charts or chart elements were added to the standard chart palette in Excel 2013. Sad smile