PowerPivot Nuggets - Part 16 - Berekening Voordeel Alle Aard (VAA) Bedrijfswagens

In Belgium there is a lot of buzz going around the higher taxes on company cars.  In Dutch it's called "Voordeel Alle Aard".  The goverment changed the way this tax is calculated.  Everybody with a company car in Belgium is now trying to find out wether he will pay more or less taxes.  Although we know the answer in advance :-)

Time to put this into a PowerPivot workbook.  The workbook uses the concepts I described in my previous blogpost PowerPivot Nuggets - Part 15 - What If Analysis with Slicers.

The tax used to be calculated on two parameters: CO2 emissions of your car and the distance between your house and your workplace.

In DAX the formula looks like this:

IF(HASONEVALUE(CO2) && HASONEVALUE(CatalogusPrijs) && HASONEVALUE(ForfaitAfstand) &&HASONEVALUE(Leeftijd)

;VALUES(CO2[CO2 Uitstoot])*0,00237 * VALUES(ForfaitAfstand[ForfaitAfstand 2011])
;BLANK())

The new formula is more complex.  CO2 emissions, age and the list price of the car play a role. In DAX it looks something likes this:

IF(HASONEVALUE(CO2) && HASONEVALUE(CatalogusPrijs) && HASONEVALUE(ForfaitAfstand) &&HASONEVALUE(Leeftijd)
;

   (( VALUES(CatalogusPrijs[CatalogusPrijs])

   * (((( VALUES(CO2[CO2 Uitstoot])-95)*0,1)+5,5)/100))*0,857143)

   * VALUES(Leeftijd[Pct])
;BLANK())

Since the "Voordeel Alle Aard" is minimum 1200 EUR/year I need to write a wrapper around this calculation.

 Jaarlijkse Bijdrage Nieuw:=IF(Bijdrage[Jaarlijkse Bijdrage Nieuw Hidden] < 1200;1200; Bijdrage[Jaarlijkse Bijdrage Nieuw Hidden])

Final step is to create a workbook with 3 slicers (Home-Work Distance, List-Price of the Car and CO2 Emission).  By choosing the right values for your car the workbook will calculate the old and new "Voordeel Alle Aard" and the difference between the two.

You can download the workbook here. You need at least Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010 RC0.

THIS WORKBOOK IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL FOR POWERPIVOT DEMO PURPOSE, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED.

PowerPivot Nuggets - Part 15 - What If Analysis with Slicers

Relationships are one of the cornerstones of PowerPivot.  In general every PowerPivot workbook should have relationships defined in the model.  However I found some very nice use cases where it makes sense not to define any relationships.

Let's take a look at this little example. Suppose we have a table with articles, cost prices and sales prices.

I want to see what happens with my margin (Sales Price - Cost Price) when I start giving discounts.   I create a second Discount table with possible discounts.  I can't create a relationship between the two tables because they don't have any columns in common.

I place the discounts on a slicers and now I  need to find the "selected discount".  Since there's no relationship between the tables the built-in filter concepts of PowerPivot don't apply here.

The DAX VALUES() function is my rescue.  VALUES() returns a one-column table that contains the distinct values from the specified column.  It doesn't make sense to calculate the discounted sales price when more than one discount is selected .  So I need to write a little wrapper around the VALUES() function to return BLANK() if more than one values is selected.

SelectedDiscount := 

           IF(HASONEVALUE(Discount[Discount])=1;
                      VALUES(Discount[Discount])
                     ;BLANK())


Now calculating the discounted value is very simple: SUM(Article[SalesPrice])*(1-VALUES(Discount[Discount]))

DiscountedPrice :=

           IF(HASONEVALUE(Discount[Discount]);
                      SUM(Article[SalesPrice])*(1-VALUES(Discount[Discount]))
                      ;BLANK())

And similar for the margin after discount in percentage:

MarginAfterDiscount:= 

                      IF(HASONEVALUE(Discount[Discount]);
                      (Article[DiscountedPrice]-SUM(Article[CostPrice]))/SUM(Article[CostPrice])
                      ;BLANK())

Now by simply clicking on the discount slicer I can see the effect of the discounts on my margin.

PowerPivot Nuggets - Part 14 - Generate a list with all your DAX calculations

As your PowerPivot workbooks grow larger and contain more calculation the urge for some kind of documentation becomes stronger.  In this PowerPivot Nugget I will describe a simple way to generate a list with all your DAX calculations.

The technique uses the internal dynamic management views of Power Pivot.  The dynamic management views are internal objects in PowerPivot that hold a lot of useful information about your PowerPivot model.  In his blogpost Querying PowerPivot DMVs from Excel Chris Webb describes step by step how to use them.

I will show you a shortcut that only takes a few mouse clicks.

 Step 1 - The preparation - Create an .ODC file with the connection and query to the embedded PowerPivot model

Option 1

• Download the connection file from Skydrive "PowerPivot Get All DAX statements.odc"
• Make sure you have the extension .odc correct
• Save it in an easy to remember location (e.g. My Data Sources).

Option 2
Create the ODC file from scratch as described in the blogpost by Chris.  There are 2 important parts in the connection file you need the change.

The connection string to the internal PowerPivot model:

Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;
Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;
Optimize Response=3;Cell Error Mode=TextValue

and the query to retrieve the DAX expressions:

SELECT DISTINCT [TABLE],  OBJECT_TYPE, OBJECT,    EXPRESSION
FROM $system.discover_calc_dependency 
WHERE OBJECT_TYPE = 'MEASURE' 
OR OBJECT_TYPE  = 'CALC_COLUMN'
ORDER BY 1


Step 2 - Generate the DAX documentation

Open a PowerPivot workbook and create a new blank sheet.
In the Data ribbon, choose Get External Data, Existing Connections.

Browse to the "PowerPivot Get All DAX statements.odc " file you created in Step 1.

 Click OK and this is the result

In the future Excel will remember the connection to the ODC file and it will only take you 3 mouse clicks to get an updated list of all your DAX calculations.
    1) Get External Data
    2) Double click on  PowerPivot Get All DAX statements in the Existing Connections dialogue box.

A real time saver!!