If we left you peering through the Power Pivot magic door after reading Part 01 and curious about what other wonders await you if you step through, here’s it:
You’ll discover new reporting powers in this alternate world. Powers you always wished you had but could only fantasize about.
Wave a DAX wand and your report is ready
Leave behind the ordinary way of doing things. Think along the lines of cooking the muggle way. What if you could wave a wand and dinner is ready? In the Power Pivot world, the magic wand itself is called Data Analysis Expressions aka DAX (If you are interested, here’s the technical definition). Waving this wand creates measures to be used in your reports.
What is a measure? It is basically a set of summarised values that has been created by a function. Here are examples of measures:
This year sales,
Last year sales,
Year-to-date (“YTD”) sales,
Fiscal YTD sales, and
Last 12 month sales.
Where do I find this and how does it actually look like in Excel?
Just in case you are using Excel 2013, the measure button is actually named Calculated Fields instead.
Why is this wand so special? It’ll take you from mere Excel user to ninja warrior status in lightning speed.
Blink and dinner is prepared.
Let’s put the DAX wand to test
Say you need to reflect the five measures above in your report by month.
Just take a minute to imagine what you’d have to do in Excel to deliver that. And imagine how long that would take. Then, imagine how long more it would take if you wanted to make them dynamically update as time passes e.g. last 12 months sales on a rolling basis.
Enter the magic DAX wand. Through the creation of measures for each category, here’s how fast you can plonk it all into a report:
Just in case you were checking if the figures add up and it’s not an illusion:
The cool thing is that the measures all re-use the same measure “total sales” in their formula.
Write It Once, Use It Forever and Everywhere
What this means is that you just Write It Once, and then Use It Forever and Everywhere! The re-usability of measures is particularly useful with certain functions. One such particular function, exclusive to Power Pivot, that lets you re-use existing measures is CALCULATE(). This very powerful function is also behind what makes the measures above possible without overly complex Excel formulas (you know, the kind with multiple formula nested within each other that stretches over numerous lines because you wanted to make it time-dynamic, reactive to data updates and changing parameters). We will talk about how to exploit it when building your reports in a later blog post .
So what this magic wand really means in the real world is that you can:
Cut down repetitive work. You no longer need to re-setup reports differently just to show different time horizons (e.g. by week, month, or year) and granularity of categories (e.g. by category, sub-category, or sub-sub-category).
Reduce errors. By writing formulas just once for each measure and then re-using them, you lower the risk of making careless mistakes. And when you do, the errors are a lot easier to trace given your obvious bread crumb trail.
Focus on getting insights from your data. Now that your time and more importantly, your mental bandwidth, is freed up, you can focus your efforts on preparing for the Very Important Meetings with your bosses and teams based on your analysis.
Don’t you agree that this opens up a whole new exciting way of getting insights from our data? Ready to walk through the Power Pivot door and grab that wand?
Learn to wave that wand right at our upcoming class run. Comes with a FREE 90-minute personalised consultation to help you use what you learn at work!