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?

image

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:

  1. Work out the effective tax date in a Calculated Column
  2. Pull in the effective tax rate into the sales table
  3. 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:

image

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:

image

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):

image

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:

image

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:

image

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:

cannot be determined in the current context.  Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies.

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:

image

Until my tax changes mid-month. At that point, things go sideways:

image

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 Smile

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

7 Responses to Guest Post from Ken Puls: Determine Effective Tax Rate

  1. Eric Hutton says:

    Nice post Ken –
    Just a little nit-picky comment on nomenclature – it is actually the “statutory” – or a combined statutory rate – which is changing to 5% or 12%.
    The “effective” rate would be a something different. Say you had an invoice that contained some items subject to both taxes and some items subject to only GST, that invoice would have an effective rate somewhere between 12 and 5 percent then. To build an effective rate calculator you would need to have product codes in your invoice and a tax rate schedule for those product codes.

    • Ken Puls says:

      True, Eric. For us it’s made no difference as everything we sold was HST taxable, but going back to the GST and PST model, you’re absolutely correct.

      • Eric Hutton says:

        And this is where the real beauty of PowerPivot comes into play… You could still do most of what you described with VLOOKUP (provided you don’t have more than a million invoices) – but once you want to cross-reference a tax rate schedule by date and product type, things get a little trickier in normal excel, but are easy-peasey in PowerPivot – drag and drop the date column and the product columns and you’re done! (You could still use VLOOKUP by forming a combined date-product-type reference column, but it is ugly).

  2. Eric Hutton says:

    … oh, and because you no longer get input tax credits for the PST you pay on your inputs, you could get really fancy and work out the effective rate that comes from having embedded sales tax tax on your products (assuming you fully pass those costs along). I worked out what that meant for the effective sales tax rate in Ontario years ago, and the impact of that embedded sales tax was substantial.

  3. Russ says:

    Great post Ken thanks as always!

  4. Bryan says:

    Can you explain why the total for the measure you created is $112,832.11 instead of $79,695.10?

    • Ken Puls says:

      Hi Brian, yes…

      That measure was using the following syntax: =ROUND(SUM(tblSales[Amount])*MAX(tblSales[Effective Rate]),2)

      Since the grand total lists all records from both March and April, it is multiplying each record by the max rate across all those records; 12%.

      Plainly it wasn’t truly fair to say the measure would work above, as it worked for the individual rows (providing no mid-month tax change), but more DAX would have been needed to ensure the total was correct.

Leave a Reply