Building an Excel 2013 Percentile dashboard without PowerPivot or a PivotTable

At the end of my Playing DAX Percentiles on the mean (or is that median) Streets of Ireland post I suggested that plain old Excel (POE) might be a preferable alternative for this particular problem rather than using PowerPivot. In fact, for many Excel users, PowerPivot will only be an option when using a PC hosted client-side workbook. Excel 2013 will allow workbooks to be published on the web and also be accessible via native apps on Windows 8 tablets (and eventually iPad & Android native apps if rumours are to be believed).

But, PowerPivot (aka Data Model) functionality will only be available to those with an enterprise-class SharePoint licence (for web publishing) and not at all for native-app-deployed workbooks. So, knowing how to use POE to construct dashboards continues to be a skill worth having.

But how to construct a responsive percentile calculating dashboard sans PowerPivot?

You might think, no problem, I’ll use a traditional PivotTable; alas, like PowerPivot, it lacks the ability to calculate percentiles as standard; but, unlike PowerPivot, offers no method of constructing a DIY measure to do so!

Next up, you might look at the SUMIFS family of “pivot” functions, but they too are limited to the usual aggregates; and SUBTOTAL is likewise limited to the usual suspects.

Luckily, via the (black!) magic of array formulas there is a way.

In fact, the “trick” below when shown to me by a “civilian” datasmith many years ago, convinced me that I should perhaps invest some time getting my head around this powerful “array formula magic”.

If you’re already comfortable with array formulas and are wondering if DAX is too complex to master, don’t worry, you’ll have little trouble mastering DAX. Likewise, if you’re a DAX whiz, you should check out array formulas.

Basing the dashboard on the Property Register converted to an Excel Table (a 2007+ feature that many are still unaware of) enables the use of  Slicer selectors in Excel 2013 to quickly give a dashboard feel. It’s still possible to use the Excel Table filters directly on the table (the built-in date and text filters are particularly useful).

Also, in Excel 2013, a chart can be directly “animated” by a range/table without the need for a PivotTable cache, again making the building of “PivotTableLess” dashboards easier.

A lot of this can also be accomplished in sub 2013 versions of Excel (including, most importantly, the array formula “trick”) but Excel 2013 just makes it all so much easier and, of course, the ability for every user (from Home to Pro) to save and/or publish workbooks via “the cloud” is a major advance (big thanks to Google Docs, without you this might never have happened 😉 ).

UPDATE:

See David Hager comment, there’s a new 2010+ AGGREGATE function which has many more options than SUBTOTAL, including Percentiles, so you can ignore the trick below if using 2010/2013. The trick would still be useful to supply an array to a bespoke formula (I used it first to calculate a particular type of weighted average) or to functions such as IRR or XIRR, so still useful to know even in modern versions of Excel.

UPDATE:

See the TheDataSpecialist’s comment for a even better modern make-over of the SUBTOTAL(3, trick.

Below is the formula to calculate a median using only “visible” rows within a filtered table/range. Note: it’s an array formula, so it must be entered using the CTRL SHIFT and ENTER keys.

{=MEDIAN(IF(SUBTOTAL(3,OFFSET(Prices[Price],ROW(Prices[Price])-MIN(ROW(Prices[Price])),,1)),Prices[Price],””))}

I used this Excel formula beautifier to make it more readable.


MEDIAN
(
    IF
    (
        SUBTOTAL
        (
            3,
            OFFSET
            (
                Prices[Price],
                ROW
                (
                    Prices[Price]
                ) -
                MIN
                (
                    ROW
                    (
                        Prices[Price]
                    )
                ),
                 ,
                1
            )
        ),
        Prices[Price],
        ""
    )
)

This logic behind this is explained here, but essentially, the OFFSET() portion returns either a filtered range of one or zero rows (counted by the SUBTOTAL(3,…) part). if one ,it’s a visible row, if zero, it’s not visible, so ignore.

If this makes no sense (it didn’t to me the 1st time I saw it), then don’t worry, just make a note of it and use the trick in blind faith :).

You can see this in action in “the cloud” here as a read-only “Excel Web App”, (you can also download the workbook to see its internals).

If you’re reading this sometime in the future the above link may not work as it’s published using a beta version of Office 2103, here’s a direct link to the Excel 2013 workbook.

And if you haven’t yet installed Excel 2013, here’s a cut-down 2007/2010 version.

Advertisements

6 responses to “Building an Excel 2013 Percentile dashboard without PowerPivot or a PivotTable

  1. It’s been a long time since I have seen anyone use this, so I am happy that it is still useful. BTW, I have not used the AGGREGATE function yet with this technique, and since there are some new arguments that SUBTOTAL does not have, it might be worth a look.

    • Hi David,

      A lot of these old POE tricks ( & even older POE tricksters 😉 ) might get a new lease of life as more Excel content migrates to the web and to “native app” deployments where add-ins & VBA will not be an option (JavaScript Office Apps offers a much limited interaction between code & workbook).

      Wasn’t aware of this 2010+ AGGREGATE function! Learn something new every day.

      I tried it out and it works. “Ignore Hidden” will ignore “non visible” rows (hidden in this case obviously has a different meaning from SUBTOTAL).

      Very useful; the SMALL & LARGE options could be particularly handy, and as it doesn’t require the dreaded CTRL SHIFT ENTER to work it can be used as a formula in an Excel Table. Brilliant!

      Tom

  2. Nice use of SUBTOTAL to determine if a cell has been filtered or not. Horrible to debug, though, due to the behaviour of OFFSET in that case. Based on this idea, you could add a calculated column to your table
    [Visible Price]=IF(SUBTOTAL(3,[@Price])>0,[@Price],””)
    and then calculate the median this way
    =MEDIAN(Prices[Visible Price])
    No array formula needed. The column can also be hidden.

  3. Where can I find an Excel 2010 or 2013 percentile WITH PowerPivot or a PivotTable?? Does it exist??