New Twist for Dynamic Segmentation: Variable Grain Control

November 6, 2014

By Avichal Singh [Twitter]

Dynamic segmentation or banding has been covered in PowerPivotPro articles in the past and in beautiful detail by the Italians – Marco Russo, Alberto Ferrari (these folks are literally “off the charts!” in Matt’s representation of Power Pivot skill levels :-) ).

It involves grouping your data in specific numeric bands or segments; for example looking at your sales data based on the price range of your products. You have a long list of price points for your products, instead of looking at each price point individually it would be nice to group them into segments say to represent the low, medium and high price items in your catalog.


Hundreds of products at different list prices…  =>  Grouped based on their List Price Range

Variable Range Selection

That is great, however it is hard to predefine segments that would work well in all scenarios. As your data changes over time, or as users slice and dice your existing data (e.g. filter to a specific region, product category or year) the segments may prove to be either too granular or not granular enough. In the case below, the predefined range does not have enough grain or detail and pretty much everything ends in one bucket ($3000-$4000).


Predefined Segment Ranges may prove too granular or not granular enough
as you work your data

What if your segments had a range of options from high to low granularity, so that you could choose the right segments based on the data and your need!


Range can be chosen to show 1000s, 100s or 10s based on dataset

Download File
Watch on YouTube or keep reading…

Read the rest of this entry »


5 common mistakes made by self taught DAX students

October 30, 2014

by Matt Allington

I am well under way in my career as a PowerPivot Consultant, Trainer and Workbook Hosting Provider.  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 of this entry »


Mini-Post: Getting Ready for the Seattle Class Nov 11-12

October 29, 2014

By Avichal Singh

Getting amped up for our first class in Seattle! Just got the box of books from Rob, which we hand out at the class. Check out all the other goodies you would get :-)

IMG_4902

The Excel to Power Pivot workshops have been a blast, but the class is going to be even more fun. Just looking forward to meeting a whole bunch of cool new people as well, who are all excited about Power Pivot (we’ll get you excited, if you aren’t yet). Everyone learns in their own way, however

  • Live class is a terrific way to jumpstart your learning
  • It is amazing how much easier it is to really absorb and cement the concepts, even if you have been using Power Pivot for a while
  • It is also a great networking opportunity. Some of these folks may be your new Excel friends for life :-)

    Speaking from my personal experience here – after attending Rob’s class even with 5 year’s worth of Power Pivot experience – and I have heard the same from Rob and Matt. See you soon!

    Click Here to View Class Details & Registration


  • Insight Center: Azure-Based SharePoint / Power Pivot Server (Joint Solution with Microsoft)

    October 28, 2014

    Post by Rob Collie

    Everyone Needs a Power Pivot Server.  The Flowchart Just Got an Exciting New Option.

    Your Server Options:  Power BI Online, On Premises / Do It Yourself,
    Third Party Cloud, and Now…  Insight Center!


    Our Continuing Mission:  A Power Pivot Server in Every, Um, Driveway

    Everyone, sing it with me:  “I’ve been wor-king on The Flow-chart, aalll the live-long daaay…”  Yes, The Flowchart.  The “yellow brick road” that helps lead your org to the Power Pivot server option best-suited for you.  (Why do you need a server?  Because it’s YouTube for Workbooks.) 

    Drawing the flowchart is, of course, the easy part.  Making sure that it “ends” in a variety of dependable options – that fit varying budgets, infrastructures, and org sizes – well, THAT is the reason why the flowchart is taking this much time.

    But said flowchart is getting closer – MUCH closer – to being ready.  We have been busy little beavers here at PowerPivotPro.  Crusaders for justice, as we oh-so-modestly think of ourselves, rarely get to rest.  The shortage of Power Pivot servers in the world is a humanitarian issue in our eyes.  (Yes, this paragraph was tongue-in-cheek.  Well, partly anyway.)

    Until now, you’ve basically had three classes of option:  1) Install and Run Your Own Servers, 2) Subscribe to Power BI Online, or 3) Lease Space From Third Party (non-Microsoft) Cloud Providers.

    All three of those options are good, viable choices, and remain so.  The Flowchart will soon help you choose between those, depending on Best Fit.  But recently it’s ALSO become clear that some organizations would benefit greatly from a Fourth Option.

    And with that realization…  Insight Center was born.

    Already Gaining Traction with Microsoft’s Enterprise Customers

    Read the rest of this entry »


    Power Pivot Job Opp in Washington DC Area

    October 27, 2014

    Post by Rob Collie

    image

    Is This You?

    Join one of Microsoft’s Most Progressive-Thinking Customers

    My friends in Microsoft’s Mid-Atlantic region have asked us to help connect one of their customers with Power Pivot talent.

    And, you know us.  We like this sort of thing Smile

    High-level details:

    1. Washington DC Area.
    2. Little or no travel required.
    3. Power Pivot skills a must.  Broader knowledge of the Power BI stack a plus (but not a must – the rest of the stack is quick to pick up).
    4. Strong communication skills and ability to understand the human/biz side of the equation.
    5. Ready to change the rules of the game & revolutionize the world of data (OK, I added this.)

    Drop us an email at careers@powerpivotpro.com with resume, CV, and/or questions.


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

    October 23, 2014

    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 of this entry »


    In-Person Power Pivot Class in Seattle, Nov 11-12 Signups Open

    October 22, 2014

    Intro by Rob:  You Will See What I Mean

    Last week I explained why Avi is the Right Guy For the Job, and mentioned that he might be a better “me” than me.

    Everyone in the Seattle area now gets a chance to see what I mean, as he will be teaching my official class there in November.  (He joins Matt Allington as the only two human beings I have certified to teach this class, other than myself, and it’s well-deserved company for both gentlemen).

    Enough from me though, I’ll let Avi take it from here.

    PowerPivotPro Comes Full Circle, Returns to Seattle

    image

    Click for Info on the Seattle Class

    Rob started his Power Pivot journey here in Seattle as one of the founding engineers for Power Pivot. I feel honored to bring that full circle and offer the next  Power Pivot class right here in Microsoft’s backyard. I have spent past several years at Microsoft, building large scale BI systems based on Power Pivot; and training internal and external users alike. Read or watch my Power Pivot journey or read all my posts.

    Read the rest of this entry »


    Excel to Power Pivot: Cross the Gap, Change the World

    October 21, 2014

    by Avichal Singh

    Oh boy! What an intro…thanks Rob. “A better me than me”…now I would need to live up to that. Let’s get to work.

    Change the World

    What happened to ‘Cross the Gap’ you say. Well, that’s covered later. This is more important, plus we are never about doing things in the expected order (e.g. we like building BI faucets before plumbing Smile).

    #1 challenge with Power Pivot is awareness. You can generalize that for Power BI, although each component of Power BI also has it’s unique challenges (e.g. Power Query).

    #1 road block in spreading awareness is Power Pivot availability.

  • Excel 2010: Power Pivot is a free add-in
  • Excel 2013/Office 365: you need to be a maze runner Smile (see illustration below)

    Either the whole world should downgrade back to Excel 2010 or we can beseech Microsoft to do something about Excel 2013/Office 365. I tossed a coin and the latter won out. So here it is folks:

    Change.org Free Power Pivot!

    CALL TO ACTION: Sign yourself and recruit others to support the Change.org Petition to Microsoft to “Free Power Pivot!”. Make Power Pivot freely available in Office 2013, Office 365 (and beyond)

    Check it out, sign the petition, ask your friends, neighbors, heck even call your Grandparents.

    One clarification though, the request to Microsoft is
    - To make it “freely available
    - and not necessarily “free”, as in a free handout
     

    If you need more inspiration, read

  • Bill Jelen (Mr Excel)’s lament

  • Comments on the original post: “Hey, Who Moved My (PowerPivot 2013) Cheese?”

  • Scott’s comment on the petition says it all “Amazing product, amazingly hard to getRead the rest of this entry »

  • Reinventing an Industry: Welcome Avi Singh!

    October 16, 2014

    Post by Rob Collie

    Our Merry Band Has Found Its Luke Skywalker

    GENERAL MADINE:  “General Solo, is your Strike Team Ready?”
    SOLO:  “We’re making huge strides.  The Death Star is Toast.”

    Avi Singh Joins PowerPivotPro Full-Time!

    Avi SinghFile under “Coup, Major.”  Folks, this is a CRAZY cool moment for us at PowerPivotPro.  We like to think of ourselves as the Rebel Alliance of Data, and seriously, we feel like we’ve found our Luke Skywalker.

    That’s Right:  Avichal (“Avi”) Singh has left Microsoft to become a full-time member of the team here at PowerPivotPro.  Microsoft’s loss is our gain, which is…  ALSO Microsoft’s gain, ultimately.  Because in this role, Avi is going to “light up” people and organizations all over the world.  As opposed to performing data miracles strictly within the internal Microsoft org, he’s going to be helping MANY OTHERS perform their OWN miracles.  With MS’s platform, of course, which is great for them.

    So hey Microsoft, please don’t be TOO upset with us for poaching him.  But really, we did nothing of the sort.  He um, kinda stalked US.  Let me explain.

    To Build A Different Kind of Firm, You Need a Different Kind of Person

    Avi Singh With Hand on Heart

    I had just finished a presentation at BA CON ‘14 in San Jose, California – a presentation I had opened with the theme of “I have the best job in the world – I work in data, and I get to spread HAPPY wherever I go.”  (Which is the truth, btw).

    Out in the hallway after the talk, I met Avi in person for the first time.  Like me at the time, you probably know Avi primarily through the excellent guest posts that he has penned from time to time. 

    The written word does not do justice to the “in person” version of this human.  He is one of the absolutely most sincere, genuine, and positive people I have ever met, and that was clear in the first two minutes.  (If I say he’s in the Donald Farmer Zone, I mean it as a compliment to both gentlemen).

    What followed was LITERALLY a “hand on heart” moment.  The very first thing Avi said to me, in reaction to the talk I’d just given, was to zero in on that “spreading HAPPY” theme.  He said something like “That’s so true, I’ve seen it!  And I feel it…  in my heart.”  And to emphasize that last part, he lightly bumped his fist to his heart.  That moment was so incredibly genuine, and conveyed several years’ worth of information in an instant:  this guy was special.

    You kinda had to be there to see why that was so impactful, so convincing to me, but if you get to meet Avi in person, you will see what I mean.  And I think you will ALSO see what I mean in the coming months, as you hear from him more often on this site and in other media.

    “Different” Kind of Firm?  “Reinventing” an Industry?

    Read the rest of this entry »


    Power Query Adds SalesForce Connectivity: Totally Awesome, But Trouble Looms Ahead

    October 14, 2014

    Post by Rob Collie

    SalesForce Data Into Power Pivot / Power BI?  YES!

    This is what it looks like when Microsoft Does Something Epically Awesome…
    …But Dark Clouds Loom – Read on to Learn Why

    We LOVE This!

    Seriously, we do.  It’s AMAZING.  Multiple of our clients are going to jump all over this.  It’s going to change their culture – AGAIN.

    If Power Query can connect to it, that lets you pull SalesForce data directly into Power Pivot.  No more export, save, import/paste/etc.

    SalesForce Data Into Power Pivot / Power View? YES!

    Power Query Can Import SalesForce Data Directly Into Power Pivot, Which Means You Can Then Visualize in Power View, For Instance

    So, the primary point of today’s post is to make you aware of this new capability, and tell you how to get it.

    Read the rest of this entry »


    Reminder: Live Class in Philly in < 2 Weeks

    October 10, 2014

    Post by Rob Collie

    PowerPivot University - Philadelphia

    Final Reminder:  I Will Be in Philadelphia Oct 21-22, Teaching a Two-Day Class
    (at the Microsoft Facility in Malvern, specifically)

    1. More Details
    2. Blog Post on Why You Should Attend
    3. Register

    See you there!


    How about a FIRSTDATE()?

    October 9, 2014

    Guest Post by Willem van Dijk

    Intro

    I am, like most (if not all) visitors to this website, an addict for information –> data –> truth –> Εμπειρία <-hope that sounds like Greek to you…

    My journey into Power Pivot has been one of uphill struggles and downhill thrills. It’s been brief moments of ego feeding, immediately followed by lengthy bouts of meekness. One moment I am Indiana Jones going for his gun in The Raiders of the Lost Ark directly followed by the same scene in The Temple of Doom


    Where did my DAX go?

    Read the rest of this entry »