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… 
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:
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?
Unfortunately, you can’t use good old VLOOKUP since PowerPivot doesn’t have a DAX vlookup function. So how DO you get the effective tax rate?
The way I approached the issue was to take 3 steps:
- Work out the effective tax date in a Calculated Column
- Pull in the effective tax rate into the sales table
- Created a simple measure to calculate the tax burden
I should be clear that there are other methods, but this made the most sense to me to be able to build it up gradually, without getting whacked with any of those frustrating “lack of context” errors that you’re probably familiar with if you’ve started the PowerPivot journey.
Getting the effective tax date
My Calendar table has 3 columns: Date, Month (a text representation for the PivotTable) and Month_Num (to allow me to sort it in month order rather than alphabetically.)
To generate the date of the tax rate I want, I went to tblCalendar and inserted a new column called “Tax Effective” using the following DAX:
=CALCULATE(MAX(tblTaxRates[Date]),
FILTER(tblTaxRates,tblTaxRates[Date]<=tblCalendar[Date]))
So basically, I’m looking for the max date from the tax rate table where the tax rate table has been filtered to only include dates less than or equal to the calendar date. MAX could be SUM or AVERAGE in this case as well since there is only ever one tax rate on a given day.
That returns me the effective date as follows:
So this is cool, I’ve now got the date right in the calendar table.
Getting the effective tax rate in the Sales table
Next up, I want to get the effective tax rate into the sales table, right beside the sales amount. To do that, I create a relationship between my new “Tax Effective” column and the tblTaxRates Date column:
With the relationship set, we can go to the tblSales table and add a new calculated column called “Effective Rate” using the following DAX:
=RELATED(tblTaxRates[Rate])
And voila! We’ve got tax rates (the arrows display where the effective rate should – and do – change):
And with that in place, we can very quickly create two calculated columns to see exactly what is happening on an invoice by invoice basis:
Tax: =ROUND([Amount]*[Effective Rate],2)
Gross Invoice: =[Amount]+[Tax]
Creating the Measures
Next up, we can create three simple measures to display the appropriate totals in the PivotTable:
Net Invoice: =SUM(tblSales[Amount])
Tax Burden: =SUM(tblSales[Tax])
Total Invoice: =SUM(tblSales[Gross Invoice])
Avg Rate: =([Tax Burden]/[Net Invoice])
Which will give us the following:
And there you have it. That’s essentially how we replicate a VLOOKUP using PowerPivot. A bit more complicated, to be sure, but overall it’s not too bad.
Note from Rob: there’s a new LOOKUPVALUE() function that may help here too but I have not used it myself yet. I also didn’t think of it when Ken and I were talking this week. I’m too spoiled – I have “an Anthony” who does most of my calc columns for me in the database.
Columns vs Measures
Rob recently published a post on this very topic, and it was something that I had to work through here as well. Since it’s something that people may also struggle with, I thought I’d continue walking through that part of the journey as well…
To be sure, it’s very easy to replace the Gross Invoice column with a measure. Simply delete the column from the tblSales table and add a new measure:
Gross Invoice: =SUM(tblSales[amount])+SUM(tblSales[Tax])
This will work just fine. So why the column then?
One thing we do know about this data is that each transaction is unique, and none of it will ever need to dynamically update based on the filter context being applied by a Pivot or Measure. (The gross invoice, no matter how we slice it is still the gross invoice.) So we don’t lose anything by not going to a measure here.
To me, as an Excel pro, there is something else that I like about this approach. I like to be able to see my tables of data, rather than work through the conceptual cloud to try and visualize what I think is happening. It adds a level of certainty for me.
At the end of the day though, column or measure doesn’t make a lot of difference for Gross Invoice. Either are easy to generate, so it doesn’t really matter which you use.
The Tax column is bit of a different story though. In order to generate our measure, we have to wrap our column names in some kind of function. That means that trying to use the following logic in a Measure, which we’re used to from Excel formulas, won’t work:
Tax: =ROUND(tblSales[Amount]*tblSales[Effective Rate],2)
Instead it gives one of my most dreaded error messages:
So the calculated column immediately solves the context issue for us, without us having to think too hard. (Personally, that reason alone is enough for me to crunch these in a column and skip the measure.)
Okay, so what if I do try to go further with the measure and put in the recommended aggregation function? The answer is that I’m going to get some odd results. Take either of these, for example:
=ROUND(SUM(tblSales[Amount])*MAX(tblSales[Effective Rate]),2)
=ROUND(SUM(tblSales[Amount])*AVERAGE(tblSales[Effective Rate]),2)
The results seem to be pretty close, out just a little bit due to rounding:
Until my tax changes mid-month. At that point, things go sideways:
Oh brother… It will obviously take a lot more DAX to get that to work properly!
And that’s the point at which I circled back and asked what I was really trying to accomplish here.
Yes, we can build a measure that will work to correctly calculate the Tax Burden, but it’s going to get much more complicated as, with mid-month rate changes possible, we need to be able to first correctly calculate the taxes day by day, then aggregate them into totals like March and April.
Note from Rob: I wouldn’t use a measure for this calculation either – Ken’s instincts are inline with my “rules.” But David Churchward might use a measure. He’s been up to some crazy stuff in his lab over there in England lately
To me, it didn’t seem to add any value, especially when I had a perfectly valid implementation already in place that does the job without that dog and pony show. 
