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.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 4 Comments

  1. Nice! Who thinks of these things?! We find ourselves among greatness!

    I just came across another use for CUBE FORMULA’s, as well. Andy – over on Kasper’s Blog – was asking about how to find the MAX() across multiple columns (he was looking at five different ones, and the nested IF’s get ugly!), and the DAX MAX() isn’t really up to that task.

    By creating a DAX measure =MAX() for each column, you can pull the resulting values forward into EXCEL using CUBEVALUE(), then just use an Excel-side MAX() of those cells. Problem solved!
    And, you can add arguments to those CUBEVALUE() formulas to respect your slicers, if needed.

    Love this stuff!
    Thanks for being willing to share all these ideas, Rob.

  2. Can’t wait to see what you have up your sleeve for CUBEVALUEs. I use them daily in my scorecards and save processing time by about 90%.

  3. I would like to see some more posts here about CUBE formulas.

    One other nice thing you can do with CUBE formulas is that you can use more than one slicer of the same kind in a report. We have a variance report with 2 measure columns and a delta column. Which periods to compare the user can select over 2 period slicer (Try to do that with a pivot!), which period type (Actual/Forecast/Plan) over disconnected slicer.

    1. Rob if you want I can send you a short example file to demonstrate what I mean with above post and you can decide if its worth to included in your “using cube formulas” series or not

Leave a Comment or Question