PowerPivotPro

PowerPivotPro is Coming to Boston

May 15 - 17, 2018

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MAY 15 - 16

Foundations: Power Pivot & Power BI

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work. Two Days in our class and you are EMPOWERED!

Overview:

  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
Boston Public Training Classes - PowerPivotPro
Boston Public Training Classes - PowerPivotPro

MAY 15 - 16

Level Up Series: Advanced DAX

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!

Overview:

  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.

MAY 17

Level Up Series: Power Query for Excel & Power BI

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!

Overview:

  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
Boston Public Training Classes - PowerPivotPro
PowerPivotPro Logo

Schrodinger’s Pivot, or Why CALCULATE() Should Be Your Favorite Function EVER.

Post by Rob Collie

Schrodinger's Cat Has Relevance to Power Pivot and Power BI

In the Classic Physics Thought Experiment, Schrodinger Hypothesized a Cat That Was Simultaneously Dead AND Alive.
(But here we will use the more humane “Simultaneously Green AND Grey.”

Back to Basics!

PowerPivotPro.com celebrated its 5th birthday back in November.  Over 5+ years, we’ve progressively covered techniques with an increasing level of sophistication.  That’s pretty natural – we ourselves have become more skilled over time, AND there’s a tendency to not want to write the same post twice.

But today I want to drive home a basic point, one that will help “recruit” Excel pros into the Power BI camp, AND that will help “crystallize” a few things even for the longtime DAX practitioners.

Schrodinger’s Cat – A Classic Battle of the Nerds

imageIn 1935, physicist Erwin Schrodinger wanted to show Albert Einstein how wrong he was.  Einstein had recently published a paper that made an astounding claim about the nature of subatomic particles.  If those claims were true, said Schrodinger, even “big” everyday stuff, like cats, could also behave in that same outlandish way.  Which made Einstein look kinda silly, in Erwin’s mind.

He proposed the idea of a cat that was both simultaneously alive AND dead, and basically said “See, Albert?  Alive AND dead is clearly impossible, so your theory is junk.”  See here for details.

But modern quantum physicists actually think the cat experiment does NOT disprove Einstein’s claim.  In fact, they think Schrodinger’s Cat demonstrates that the universe is fundamentally a MUCH stranger place than we typically think.

In short, the concept of “impossible” is subject to re-evaluation.

This PivotTable is Simultaneously Filtered AND Unfiltered

Read the Rest

Repeat Customers in DAX: Three Flavors

Post by Rob Collie

Repeat Customers in Power Pivot / DAX:  By Number of Transaction Lines, By Number of Distinct Orders, and Allowing for Cross-Year Return Customers

In 2004, There Were 2,561 Customers Who Bought Something in the Southwest.
But How Many of Those Were Repeat Customers?  Depending on How We
Define “Repeat,” We Can Get at Least Three Different Answers.

A Right Turn at Albuquerque…

I sat down today to write about “Disconnected Cube Formulas” – yes, you heard that right.  A brand new technique that I think has some pretty exciting (yet admittedly narrow) applications.

But along the way, like Bugs Bunny, I ended up doing something at least as interesting.  So let’s do that one first.

Setting Up the Problem

I have four relevant tables:  Territories, Customers, Calendar, and Sales:

image

The first three are Lookup (aka Dimension) tables, and Sales is a Data (aka Fact) table.

Active Customers is a pretty easy formula:

  [Active Customers]:=

   DISTINCTCOUNT(Sales[CustomerKey])

Read the Rest

SUM, SUMX or CALCULATE()…Choices, choices!

By Avichal Singh (Avi)

When I was working recently with a client, helping her remotely – I asked her to calculate the sum for sales amount in the table. She responded whether she should use SUM, SUMX or CALCULATE?

Simple question, but not a simple answer. Or I can give you the classic lawyer response – “It depends!” Let’s review.

SUM: Simple Unmitigated Magic

The good news is that a simple SUM, would work in majority of the cases. A simple sum in the hands of Power Pivot is a powerful tool. With the magic of relationships a simple SUM can show you tricks you could never have imagined in Excel.

Power Pivot relationships mean, that you define your measures once and use them everywhere.

Power Pivot Measures: Define Once, Use Everywhere“Define Once, Use Everywhere”

Your measures conform to the shape of your pivot, so you can drag and drop any fields from your model, use any slicers and the measures would still work.

Sales:=SUM(Transactions[Amount])

image
Simple SUM() does magic in Power Pivot: Define Once > Use Everywhere in action

Only when you see the results and go…”Uh…that is not exactly what I want”, should you explore other options.

Step at a time – SUMX

Read the Rest

CALCULATE(): More Examples and Reinforcement

Guess Post by Scott Senkeresty

Intro

Rob is taking a much-needed vacation this week, so you get to hang out with me again.  Hurray for you!

When we last Became One With Calculate, I said in comments that I would “work on a visualization/graphic”.  I admit to spending far too much time trying to dream up the perfect visual, and kind of failing.  I am sure the elusive visual exists, but for now, I would like to reinforce our understanding of CALCULATE() with a few more examples.

We will again be partying with the Adventure Works, against this simple measure:
[Total Sales] := SUM(Sales[ExtendedAmount])

Example 1: Column Filter

[TotalSalesEurope] := CALCULATE([Total Sales], Territories[Continent] = “Europe”)

imageThis boolean parameter (aka: true/false parameter, column filter) says “Hey, Mr Dax Engine, I really don’t care what filter you had on Continent… cuz now it is Europe”.   Of course, we did nothing that would impact a filter on Product[Category], so each of the categories still have their own total sales.

I must admit… when I last wrote about CALCULATE(), I was thinking there was something fundamentally different and special about these true/false filters, compared to the table-style filters such as we see with FILTER() or ALL().

And indeed, they are kinda sorta almost special… in that they have a cute syntax and they have the potential to be much more efficient (in terms of speed).

However, functionally, the above measure is identical to the following measure:

=CALCULATE([Total Sales],
          
FILTER(ALL(Territories[Continent]),Territories[Continent] = “Europe”))

Read the Rest

Becoming one with CALCULATE()

Guest Post by Scott Senkeresty

Intro from Rob

Hey, it starts out simple and powerful:  CALCULATE is the SUMIF you always wished you’d had.  It works in pivots.  It’s the “anything IF.”  It’s amazing, really, how many doors it opens.

Of course, CALCULATE is designed to be powerful in ways we can’t even IMAGINE in our first day/week/month of using it.  You can spend years discovering all the things it can do – and that’s a good thing!  But sooner or later you’re going to hit something with CALCULATE that makes you scratch your head – why is it returning THOSE results?

I myself entered this twilight zone with the Precedence Project – a series of posts that I quickly abandoned.  It turns out that, practically speaking, you don’t need to achieve deep theoretical understanding of this stuff in order to achieve great results.

Below, however, Scott does a great job of resolving those mysteries.  And he does so by “channeling” two old friends who live at the base of the Alps.  Take it away, Scott…

Going to “Graduate School”

image

All right, so I’ve read Rob’s book a few times.  (Heck, I am credited as tech editor on it.)  I’ve devoured PowerPivotPro University.  So now what, I ask Rob?

“Go forth and conquer – data is your ocean,” is his answer.  He’s a practical sort of guy.  Me, though?  I’m never satisfied until I’ve completely torn the machine apart.

So, as I hinted in my last post, I went to graduate school and spent a few intense days engrossed in Marco and Alberto’s book.

 

Read the Rest