PowerPivotPro

PowerPivotPro is Coming to Atlanta

March 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.

MARCH 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

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 Austin Senseman – PowerPivotPro Partner
  • You don’t need to be an IT professional – most of our students come from an Excel background
Atlanta Public Training Classes
Atlanta Public Training Classes

MARCH 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

MARCH 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

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 Ryan Bergstrom – Principal Consultant and Trainer
  • 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
Atlanta Public Training Classes
PowerPivotPro Logo

Reverse Polarity RELATED()

 

Guest post by Ken Puls

I was recently working on an interesting thing in PowerPivot, and thought I’d share it in case it may help someone… A quick summary of the issue is that I needed to examine multiple records in a child table, and return a single result to the parent table if a condition was present; something that goes against the normal relationship flow.

Scenario Background

I have a file that tracks the purchase and sale of land, as well as the respective property taxes and assessments that we get on an annual basis. It is set up like this:

9-17-2013 2-56-59 PM

At first glance this might look complicated… Each Parcel is assigned a unique Parcel Identifier, known as a PID, a key identifier that will never change for a piece of property.  This info is then used to identify that parcel by the tax authority, the assessment authority, and our appraisers and accounting systems.  In the case above, we can see that the PID from the Parcel table links to the Transaction, Taxes and Assessments tables. From the other side we’ve got the Key_Date that links back to the three tables as well, although it links to TranDate, TaxYear and AssessYear respectively.

Read the Rest

Guest Post from Ken Puls: How to buy PowerPivot 2013, and the $30 Volume Licensing Workaround

An Amusing Solution!

In the long-running comment thread on the Who Moved My PowerPivot Cheese post, one of the recurring themes is “hey, just let me pay a small amount extra for PowerPivot in 2013, but give me a way to buy it ok?”

At the MVP Summit last week, Ken Puls mentioned that he has a way to do precisely that.  You pay about $30 for the right to buy a Volume License copy of Office 2013 Pro Plus.

I haven’t tried this myself but Ken certainly has.  Consider this a viable workaround until further notice.  Take it away Ken…

The Official Purchasing Channels

Rob recently put up a post on the availability of PowerPivot in Office 2013, and how it wouldn’t ship in all Excel SKU’s. This is a huge issue, to be sure, so I thought I’d quickly summarize the software distribution channels so you can see where you will/won’t get PowerPivot if you buy into the 2013 package.

You’ve got two ways to buy a copy of Office 2013 Pro Plus (the version that includes PowerPivot):  Volume Licensing or an Office 365 Business subscription (the Home subscriptions do NOT include PowerPivot). 

Each can be further broken down (see Chris Webb’s blog on Office 365 options here), but to keep this easy to follow, I’ve kept it to key comparable SKU’s:

image

The $30 Volume Licensing Workaround!

Now, here’s the really funny part about the above though… everything you’ve read so far would give you the impression that getting a volume license is going to be tough and expensive. It’s actually not.

Read the Rest

Guest Post from Ken Puls: Determine Effective Tax Rate

Excel MVP Forever.  PowerPivot Pro On the Rise!

Back in December I wrote about Ken Puls’ role in inspiring the book, and described him as a DAX convert (and also someone who used to intimidate me, in a good way, at MVP Summits back when I was a newbie on the Excel team).  Well I’m happy to welcome a guest post from Ken today.

I think it’s particularly valuable to hear from a) someone who is still relatively new to the PowerPivot journey like Ken  and b) someone other than me, period – since both provide a very different perspective, and that helps us learn.

So, take it away Ken… Smile

Background

In British Columbia we’ve been working with a 12% HST (Harmonized Sales Tax) for the past 1.5 years. Effective April 1, 2013, we’ll be going back to a system with a separate 7% Provincial Sales Tax (PST) and our national 5% Goods and Services Tax (GST) instead. In our case, we wanted to look at sales that will not be PST taxable under the new tax structure, meaning that the effective tax on these sales will drop from 12% to 5%.

So assuming that we have the following tables in an Excel worksheet and the name of the tax table is tblTaxRates, it’s really easy to get the effective tax rate for any date:

tax-1

We simply add a VLOOKUP to the sales table with the following formula copied down the sales table:

=VLOOKUP([@Date],tblTaxRates[#All],2,TRUE)

Easy stuff for any Excel pro. But what do you do if your sales table is in PowerPivot, like this?

Read the Rest