Cube Formulas in Power Pivot Combined with Sparklines:  AKA FrankenSpark

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:

Cube Formulas in Power Pivot Combined with Sparklines and a Chart

“FrankenSpark” Used as Part of a Larger (Redacted and Obfuscated) Client Scorecard
(Yes, the Colors Still Need Some Work)

The How-To

We’ll keep this brief since all of the techniques have been covered before:

  1. Sparklines post by Kasper de Jonge.  (So yes, my sparkline is powered by a hidden pivot).
  2. Cube formulas posts here and here and here.  Oh yeah, and here too.

So really, the key “innovation” here is just discovering that the two can be blended for a combined aesthetic effect that looks NOTHING like anything has ever seen before in Excel.

But just in case those posts leave you wondering how it was done, here is one more diagram:

Cube Formulas in Power Pivot:  A "Card Viiew"

Every Cell in this “Card” is a Cube Formula, Except for the Labels

All of the CUBEVALUE cells reference the one CUBEMEMBER cell, so that it’s easy to copy and paste a single “card” to create the others.

In general, I think it’s high time that I write a series of posts on using cube formulas to create scorecards, because I have been doing a LOT of that lately, and it’s been extremely valuable work for the clients who are deploying it.  It’s become too common to ignore – there is something VERY significant going on in this space.