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…
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:
We simply add a VLOOKUP to the sales table with the following formula copied down the sales table:
Easy stuff for any Excel pro. But what do you do if your sales table is in PowerPivot, like this?