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 ).
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.
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.
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.
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.