Two Tricks to Make Your Cube Formula Scorecards Drama-Free!

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!

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Total Sales]”,slicer_country1,slicer_country2,slicer_dim_calendar,slicer_salesrep,
slicer_contest,slicer_supplier,slicer_supplier1,slicer_supplier5,slicer_salesterritory
,slicer_salesterritor2)

Yikes! Imagine updating thousands of these! I’m going cross-eyed already!

The D-Man Innovates!

FrankenSpark! (Cube Formulas Meet Sparklines)

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)