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

A Neat Trick/Macro for More Readable Pivots

 
OK, you’ve been a good Power Pivot author and given your measures clearly descriptive names.

Your punishment is spending all day looking at pivots like this:

Your Pivot is Too Wide for the Screen

Hey, Where’s the Rest of My Information?
(Hint:  It’s in “Scrollsville.”)

Nice and Cleanly Readable Pivot

MUCH Better:  Last Two Measures Completely Visible, With Space to Spare!
(Assuming Vertical Space Isn’t a Problem, Of Course)

A Trick I “Harvested” From a Client

Awhile back I was working with a gentleman named Tom Phelan who repeatedly used a series of click mouse clicks to achieve the sort of layout pictured above.  After seeing him do that about ten times I asked him to slow down so I could see what the clicks were.

Read the Rest

FrankenSpark! (Cube Formulas Meet Sparklines)

Cube Formulas in Power Pivot Combined with Sparklines:  AKA FrankenSpark

That’s a Single Spreadsheet Cell with a CUBEVALUE Formula AND a Sparkline in It!

I was working with a client last week when a question occurred to me:

“Can I put a Sparkline in a cell that already has a Cube Formula in it?”
”Oh cool, it worked!” (Cackles Maniacally)

-me, last week

Anyway, we were off and running at that point:

 
Cube Formulas in Power Pivot Combined with Sparklines and a Chart

“FrankenSpark” Used as Part of a Larger (Redacted and Obfuscated) Client Scorecard
(Yes, the Colors Still Need Some Work)

The How-To

Read the Rest

We Have a “Crush” on Verblike Reports

image

Even Very “Sophisticated” Reports/Dashboards are Often “Couch Potatoes” in Practice
(They Sit There and Expect You to Do All the Work)

Intro From Rob

I’ve been meaning to blog this for a long time – it is, after all, one of my absolute FAVORITE things to talk about.  Now, Scott beats me to it.  But he does such a good job below that I don’t have much to add, except a few graphics here and there, like the one above.

Besides, how can I not love a blog post that starts off talking about how right I am? Smile

Guest Post by Scott at Tiny Lizard

By nature, I am a rather skeptical person.  When I first hear an idea, I generally think it is probably wrong.  This isn’t one of my finer traits and  I am sure it drives Rob insane, since he has a rather impressive track record of being correct.  Thankfully, this blog entry is not about one of the times I assumed Rob was wrong…

It is actually the complete opposite.  It is one of those times when Rob told me something and it just instantly SUPER resonated with me and got stuck in me.   I suspect it will do the same for most of you.

Nouns and Verbs

imageIf you were fortunate enough to hear Rob speak recently at PASS Business Analytics Conference, you have already heard this idea.

It is super typical for us, as report authors, to generate The Report.  The Noun.  We hand it off with pride.  And The Report has all the information somebody could possibly need to make a decision.  All of it.   Row after Row. Column after Column.  Unfortunately, even with the fanciest conditional formatting in the world, it is not clear… how is somebody supposed to look at The Report and actually… do something?

Because, at the end of the day (and yes, with a handful of exceptions like keeping the SEC happy), it is our hope that somebody looks at our reports and uses it to make a decision.  To do something.  To Verb.

Read the Rest

Custom Toooltips in Dashboards!

 
Power Pivot Dashboard Tooltips/Comments

Custom “On Hover” Tooltips on Each Cell in the Dashboard!
(The yellow dot and distortion around mouse pointer are GIF side effects and do NOT appear in Excel)

Question from PowerPivotPro School!

Got a great question the other day from Oscar, a student in PowerPivotPro School

[OSCAR]:  “Is it possible to have a tooltip in powerpivot which shows additional information based on the cells selected (or mouse roll over). the info to be displayed comes in from a table created with cube fuctions on the same data source. So the coordinates of the highlighted cells would be inputs for the cube formula and result displayed in a tool tip dynamically.”

My first thought was “no, not possible.”  Then ten seconds later, a guerilla-style hack came to mind.  And then, my reply:

[ROB]:  “Oscar you are a very, VERY bad man. I am now obsessed with this problem. There goes my Sunday.”

The Trick:  Hyperlinks to Nowhere!

Read the Rest

Bubbling Up Exceptions with “Sara Problem?”

 
Power Pivot Allows Some Amazing Stuff, Like Exception Flags that "Bubble Up!"

The Flag Appears at Top Level (Accessories), Telling You There’s a Problem Further Down.
Expand Accessories and You See the Culprit is Bottles and Cages (Specifically Road Bottle Cage)

Start With a “Sara Problem” Measure!

Quick post today, continuing the saga of Anakin Skywalker.  An extension to the bubble up ranks technique.

Let’s say you have a measure.  Doesn’t matter what it is or how it’s calculated really, except that it reports on whether there’s a problem.  It returns 1 (or Yes or True) if there’s a problem, and 0 (or No/False) if there isn’t.  Or maybe it returns a “regular” number, but when it crosses a certain line, your business has decided that is Bad.

Get it?   The measure is named “Sara Problem!!!!”  To pronounce it properly you have to add a question mark – so technically, it’s named…  “Sara Problem?

Great pun!  But I can’t take credit for it.  Back when we lived in Seattle, my wife (girlfriend at the time, and fellow Microsoft engineer) played roller derby.  She was a “Rat City Rollergirl,”  she played for a team called Grave Danger, and her skater name was Natalie Fatality.

I am not making this up.  Well, she had a teammate whose skater name was Sara Problem.

This has nothing to do with Power Pivot, or at least not directly.  But c'mon, it's badass :)

“Sara Problem” on Left, “Natalie Fatality” (Official Wife of PowerPivotPro) on Right

Back to the Formulas!

 

Read the Rest