PowerPivotPro

PowerPivotPro is Coming to Phoenix

February 20 - 22, 2018

Registration for 2018 Public Training is now open!

AVAILABLE CLASSES

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

FEBRUARY 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Kellan Danielson

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!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Kellan Danielson – PowerPivotPro Partner and Vice President of Client Services
  • You don’t need to be an IT professional – most of our students come from an Excel background

FEBRUARY 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!

Overview

  • This advanced DAX training class is taught completely in Power BI Desktop.
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language.
  • Taught by Ryan Sullivan – Principal Consultant.
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms.

FEBRUARY 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Krissy Dyess

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop.
  • Taught by Krissy Dyess – PowerPivotPro Principal Consultant and Phoenix native!
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities.
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges.
PowerPivotPro Logo

 
Calendar Chart in Excel - PowerPivot Can Do Some Amazing Things

“CalChart” – The Most Absolutely Awesome Thing I’ve Ever Done in Excel
(Data:  Fake UFO Sightings/Alien Abductions – Data I 100% Made Up)

New Chart Type Added to Excel 2010!

Yes, it’s a new chart type.  And yes, it’s been added to Excel 2010.  But not by my former colleagues at Microsoft.  This was done by me, after being inspired by another Excel pro, and with a heavy dose of formatting and sparkline assistance from another.

And it’s not some new fancy software addin or something like that.

It’s formulas.  In the normal Excel grid.

What’s so special about it?

The really nifty thing is that I did NOT manually enter calendar months into the grid.  This is all driven off of a PowerPivot date table.

In other words, this visualization responds to slicers!  I can change the measure displayed…

PowerPivot - Excel Calendar Chart Can Display Any Year, Any Month, Any Metric

Changed from Total Abductions Measure to Late Night Sightings with a Slicer Click!

And I can also change the date range displayed.  Don’t want to see 2001-2003, July-Dec?  No worries, just clicky:

PowerPivot - Excel Calendar Chart Can Display Any Year, Any Month, Any Metric

Now We’re Seeing 2009-2011, Just March-May!

In fact, I could change my calendar table to span the years of, say, World War Two and it would just work.  There is nothing special about the years and months displayed above.

Try it Out!

If you want to see the CalChart in action, just click here and an interactive version will open in your browser:

https://insights.hostedpowerpivot.com/sites/Demo/Pages/CalChart.aspx

No, Really.  Try it out Smile

Click that link above.  It won’t install anything, download anything, etc.  In fact you don’t even need Excel installed to try it out.

How Did I Do It?

I’ll have to explain in detail on Tuesday, but it uses a BUNCH of Excel features, all wired together in the way only can Excel can do things.  There was, in fact, a bunch of boiling cauldrons and bubbling test tubes in the background while I created this:

  1. PowerPivot (of course)
  2. Cube Formulas
  3. Conditional formatting
  4. Sparklines
  5. Array Formulas
  6. Named, relative, and absolute references
  7. And I used macros to help me populate the grid (rather than manually typing formulas forever)

Don’t want to wait?  Well, take a look for yourself…

Download the workbook here.  (UPDATED August 14, 2012)

UPDATE:  Part One of the Explanation

***Check out this post for a peek behind the scenes of this workbook.***

UPDATE:  Part Two – Adapting to Your Data in Thirty Minutes or Less

***Quickly adapt the CalChart for use with your data***

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 38 Comments

  1. Rob – this is the most badass thing I’ve ever seen!

    (I originally said “this is the most badass thing I’ve ever seen from you”, but I decided on the edit above).

    Bill

    1. Why thank you Bill 🙂

      In many ways I can now call my life a success. To own, even temporarily, the title of “most badass thing ever seen by Mr. Excel” is well, quite fulfilling 🙂

      You were part of the chain of events that inspired it, which is quite fitting I think. We are ALL always building on everyone else’s inspirations.

      I even got help from the SSAS crowd to help me with the MDX in the cube formulas. This is a community project. It’s only fair that I “open source” the workbook – download away! 🙂

  2. Wow, I have showed my underlings this and told them the first one to recreate it in a meaningful way that I can use gets a popcicle. Freaking awesome!

  3. As I said the other day, Excel is only as limited as your imagination. And your imagination doesn’t seem to be limited at all.

    I hope when you got it working you jumped up and yelled “It’s alive! Aliiiive!” Then Igor joined you in a hearty, steeple fingered, “MUA HA HA”.

    1. Actually, there was enthusiastic fist pumping, shouting, and profanity.

      And Igor doing his thing, of course.

  4. Insert pic of Jess Ennis, flash some gold around and GB calendar done! Send to print…

    Simply awesome. Great job RC

  5. As the formatting schlub behind Rob’s brilliance and the MDX community contribution, I can tell you that this moment punctuated for me the irresistible nature of Excel. Nowhere else does the canvas exist to encourage business users to take control of their analytical destiny. I love watching this community of like-minded business professionals explore the boundaries without the limitations of traditional IT constraints. Personally, I would love to see what you all DO with this template… Just because I am so impressed with what you do with PowerPivot!

  6. WOW, I mean Wow – I see this showing up on all the excel sites in short order – this is a paradigm shift for what can be done
    Now if I can just figure out how to utilize it or hope you have an explanation (format your data so – insert here and walla-walla – amaze your boss

    Thanks for the great work

    Rich

  7. For the backstory on our Calendar Vis Project, you can see a PDF of the original, which was done with Excel 10 using pivot tables, at http://wisconsinsafetydataportal.org/default/assets/File/wi1.pdf

    THANK YOU to Bill, aka MrExcel, and to John, Rob, Jeff, and Nan at Pivotstream for your enthusiastic and creative interest – this is a very exciting project!

    Joni Graves
    Department of Engineering Professional Development (EPD)
    Wisconsin LTAP / Transportation Information Center
    University of Wisconsin – Madison

    1. I have not tried it yet in Excel 2013. Can you tell me what happens? I was hoping it would prompt you to upgrade. In Excel 2013, do you have the PowerPivot addin active?

    1. Yes, this is what I’ve experienced too! A colleague of mine said that Rob demoed this at the PASS Business Analytic’s Conference in Chicago earlier this month. I will have to have a closer look later tonight. Looks very interesting.

      1. The CUBE* functions require the name of the data model as a parameter. In previous versions this was called “PowerPivot Data”. In Excel 2013, this became “ThisWorkbookDataModel”, which broke the formulae in this workbook.

        To fix, do a global search and replace throughout the workbook with the former being replaced by the latter.

        1. Hi Paul, an update to my earlier comment : after doing the Global Find & Replace, the #N/A disappear but when I try to change any filter, Excel gives an error message asking to upgrade the data model with PowerPivot for Excel 2013.

          1. Hi Mahit, this is normal. Once Excel updates the data model for you, I seem to remember that it will ask you to restart Excel. Once this is done and the data model name is updated, it should work for you.

          2. Hi Paul, even after the global find/replace & a restart, Excel is asking the same upgrade of the data model.

  8. Hi everyone,
    i encounter the problem with moving model to my business model – i want it to adapt it somehow in my business case.
    It just simply doesnt allow it- saying something about object reference error. can anybody help with it? i appreciate

  9. I’m trying to use your template “CalGridForBlogDownload1.xlsx” but because it is from Excel 2010 (and I have Excel 2013) it gives me an error that says “This workbook has a PowerPivot data model created using a previous version of the PowerPivot add-in. You’ll need to upgrade this data model with PowerPivot for Excel 2013”.

    Do you have a version that works with 2013?

Leave a Comment or Question