Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia.

Dynamic Age Calculation Using Measures

by Matt Allington

I was recently helping a forum member at http://powerpivotforum.com.au with a problem about how to dynamically calculate an employee’s age.  I thought a worked through example would make a good blog post as it demonstrates a further use of disconnected tables vs the more common “disconnected slicers” tables that Rob loves so much.

First let me explain the scenario.

Number of Employees Under the Age of 35

The requirement is to be able to calculate the total number of employees under age 35 years of age at any point in time.  The DAX formulae therefore need to take into account new employees starting at the company, employees leaving the company, as well as the fact that all employees get older every year.  Here is the solution I created.

Read the Rest

The Catalyst to Corporate Power Pivot Success

by Matt Allington Many businesses (dare I say most) are yet to get started with Power Pivot and Self Service BI.  Often the first person in an organisation that discovers Power Pivot doesn’t know how to navigate the political and cross functional challenges within the organisation to make it all possible.  The tragedy of this are the missed opportunities – lost time is lost money.  There are huge benefits that can flow to businesses if they could just get this happening. There are benefits for IT and the overall business, including lower IT costs, faster speed to value realisation and indeed green field analyses that would not have even been contemplated as feasible without a Self Service approach and a tool like Power Pivot. Start Line

How to get started?!

Read the Rest

5 common mistakes made by self taught DAX students

by Matt Allington

I am well under way in my career as a Power Pivot Consultant and Trainer.  And I have to say (now that I have delivered a number of Rob’s “PowerPivot for Excel” training classes) that I am finding delivering training to be one of the most rewarding things I do.

It occurred to me recently that people like me (and also Rob, Avi, Scott), that train users in PowerPivot are able to glean useful insights into the way people learn (and incorrectly learn) PowerPivot.  Today I am going to share with you 5 common mistakes that I have personally observed – maybe you will identify yourself in some of these things, or maybe you will confirm that you are doing just great.  Either way, it is worth a read to either discover a gap or confirm your skill.

But first: I have trained 2 general types of students

I have found there are 2 groups of students that sign up for my PowerPivot for Excel classes.  There are students that are very new, very green (see what I did there – green, get it?) and are using the class to get started.  They come in with very little knowledge about PowerPivot, but enough to know that this could be something awesome.  In the second group are students who have a reasonable amount of PowerPivot experience under their belt but realise there is more to it.  What is a “reasonable amount” of course can vary, but I would classify these people as “active” for 6-26 weeks with a total number of “invested hours” in the vicinity of 10 – 60 hours or so.  Often they are struggling to move forward, and this post covers the main reasons why.

I always ask my students to rank their knowledge on a scale of 0 to 10, and they normally rank themselves 0 (newbies) or about 3 or 4.

2 types

Read the Rest

5 Interactive Chart Techniques Come Together

by Matt Allington

Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas.  While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated.  I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user.  There is a link to this workbook at the end of this post.

The techniques I have used are:

  1. Disconnected slicers used to create interactive chart series
  2. Cube formulae and standard Excel to make an interactive chart title

I love these 2 tips I learnt from Rob – so user friendly.  However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.

  1. Cube formulae and standard Excel to make an interactive legend
  2. VBA and “link to source” for interactive axis formatting
  3. Excel VBA to change which Axis the series appears on.

I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below.  I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.

Now let me call out the key techniques I have used to make this workbook rock.

Read the Rest

CALCULATE ( ) Lessons from Looney Tunes

By Matt Allington

In my last post I talked about a mistake I made early on in my DAX learning journey.  In that post I showed a formula that used CALCULATE ( ) to turn a row context into a filter context (AKA context transition).  Here is a quick refresh of the relevant part of that post.

calculate post

Since this post, I have been thinking about CALCULATE ( ) and wondering how to explain “WHY” CALCULATE ( ) creates context transition – this is the topic of today’s post.

I am sure there is a wide age profile of readers of this blog, and at least some of you would remember the Merrie Melodies cartoon “Cheese Chasers” where the dog (Marc) is sitting and using an ACME adding machine to make sense of what he is observing. [I know I said Looney Tunes in the title, but there is not a lot of difference and I thought more people would know what Loony Tunes was]. Before you watch this brief 40 second clip from the cartoon, let me first set the scene.  Everything is backward; the mice don’t eat cheese, the cat WANTS to be chased by the dog – you get the idea.   Spoiler alert – I will refer to the punch line below, so don’t read on until you watch the video if you want the full 1951 immersive experience.

Read the Rest

Filters CAN Flow Up Hill – Via Formulas That Is

By Matt Allington

Intro from Rob

imageMany of you already know of Matt Allington and his background as BI Director for Coca-Cola Asia Pacific.  Matt recently flew around the world (literally) to attend my training course with the purpose of becoming accredited by me as the official PowerPivotPro University Trainer for Australia (we even have a picture to prove it – displayed at right).

Now that Matt has my approval to teach my material, I have invited Matt to be a regular blogger on PowerPivotPro.com.  This is Matt’s first post in this capacity, so over to you Matt.

Confessions of a DAX student

Matt here:  I want to share with you a simple mistake I made early on in my DAX journey, and also create awareness of how easy it is to fall into a similar trap. This post will explain the mistake and provide the solution to how you can get filters to flow up hill – via formulae that is.

If you learnt DAX the Rob Collie way (like I did) you would be very familiar with Rob’s best practice of placing the lookup tables above the data tables in the PowerPivot Diagram View.

schema

The reason Rob teaches it this way is because it is very easy for the reader to visualise the flow of a filtered table – filters flow down hill.

Read the Rest