Kasper de Jonge PowerPivot Blog

An updated version of the whitepaper “Data Analysis Expressions (DAX) In the Tabular BI Semantic Model” is available for download at http://go.microsoft.com/fwlink/?LinkID=237472&clcid=0×409.

The Whitepaper is now updated with SQL Server 2012 DAX functions and well worth the read if you are interested in DAX (which you no doubt are if you are following my blog). Thanks to Peter Meyers and Howie Dickerman for writing this whitepaper and Owen Duncan for contributing to it.

ok ok, I am much to late to the party for #meme15 by Jason Strate

From the post by Jason:

#meme15 is dedicated to discussing and sharing tips and tricks for blogging and using social media.  This can be from a personal or professional standpoint.  The intent is that through these posts by myself and others will provide some insight into how to succeed in these areas.

This month’s writing assignment was:

• Why did I start blogging?
• Why do I currently blog?

I was reading the excellent blog post by Jen Stirrup that sparked me to share my experiences as well.

Why did I start blogging?

I started blogging on November 1st 2007 to share the things I found on the internet with colleagues, customers and myself At the time  I was working as a consultant and I visited customers who were not as much into blogs and the internet as I was. This gave me the opportunity to share what I found, but at this point it was just aggregating other blogs.

Around 2009 I started writing a little bit more substantial blog post with the CTP release of Project Gemini now known as PowerPivot, PowerPivot really changed my enthusiasm to a whole new level. From this post on I started doing more substantial blog posts to share about my experiences on Gemini, this also was my way into the product team (Through Rob Collie and his blog PowerPivotPro.com) that helped me when I got stuck on Gemini. After a while blogging and twitter got me into the online community and I started doing presentations on SQLSaturday and other events. And eventually it led me across the pond to the us to work for ms.

Why do I currently blog?

Since I started to work for Microsoft my blogging situation changed quiet a bit, but I still love blogging. Why do I keep on blogging?

• Since i now have the latest information, i can share it the moment the product gets out
• I really like my ask a questions page, it gives me a good idea what you all are doing with powepivot and it gives me ideas on what to blog about.
• It allows me to test out the product with some real world scenarios
• Gives me ideas for presentations
• It keeps me connected to the twitter and blogging community. I really like the community but sometimes it’s really hard to stay connected to the world outside Microsoft.

With SQL 2012 PowerPivot we have hierarchies at our disposal, one of the most common calculations we want to do with those hierarchies is doing a ratio to parent. This blog describes how you to do that.

I started with two simple tables, one a team / chapter table, the other sales by chapter:

Now I loaded these into PowerPivot, created a reletionship and a hierarchy:

Next I use the Hierarchy in a pivottable together with a Sum of SalesAmount measure:

Now we need a new measure that allows us to compare the actual sales per chapter with the sales of the team (or of all the chapters of the same team). This is a pretty  straightforward DAX formula once you understand my second phrase “ all the chapters of the same team”. What we in DAX can do is overwrite filtercontext and that is what we can do here.

If you take the same pivottable again but now we look at the highlighted cell:

What filters are used to calculate the sum of sales?  The filters here are “Hierarchy1 = Team” and “Hierarchy2=chapter1″. Now using calculate we can overwrite filters, so all we need to make sure of is that in our new measure we never listen to the Hierarchy2 filter, or in the DAX case we always filter Hierarchy2 by ALL values in the column. The reason that we can use ALL values in the Hierarchy2 column is that the other filter is still in place, Hierarchy1 is still filtered by “Team” so it will only return values for chapters that belong to the “Team”

This gives us the following DAX statement:

=CALCULATE([Sum of Sales],ALL(Table1[Hierarchy2]))

Gives us the following pivottable:

Now it is pretty simple to add a ratio formula:

=[Sum of Sales] / [Sum of sales all chapters]

That gives us the result we want:

So that turned out to be pretty easy once you understand the concept.

But what if you have multiple levels ? This needs a little more extended “Sum of Parent” formula since we need to override all level individually:

=if(HASONEVALUE(Table1[Hierarchy2]),
                    CALCULATE([Sum of Sales],all(Table1[Hierarchy2])) ,
                    CALCULATE([Sum of Sales],all(Table1[Hierarchy12]))
     )

Here we check if we are actually at the lowest level of the hierarchy “Table1[Hierarchy2]” if that is the case we override the filtercontext with that level. Otherwhise we override with the level above. This can be repeated if you have more levels:

I got this interesting question on my ask a questions page that I would like to answer by writing a blog post.

The question was the following:

I have a table fetched from a SQL database that contains a few fields but the interested ones are ComputerID and SystemStartupTime which is the time a computer takes to boot.
I have been asked to display this information on a graph averaged by the average of each ComputerID which I did like this:
myWonderfulMeasure:=ROUND(AVERAGEX(SUMMARIZE(myTable,myTable[ComputerID],”AveragePerComputer”,AVERAGE([SystemStartupTime])),[AveragePerComputer]), 0)
The graph using this measure shows those values by Year and WeekNumber successfully using slicers to play with different variables.

Now I have to display this average on another graph by segments of 30 seconds. This means, by number of machines that booted in:
- 0 to 30 seconds
- 31 to 60 seconds
- …

I wanted to use an autocalculated column. However I lose the average per computer (as the table lists all of the boot times).
I then tried to create a new measure with a long and dirty IF:
=if(Table[myWonderfulMeasure]<=30, “0-30″, if(Table[myWonderfulMeasure]<=60, “30-60″, [..] ))
However it doesn’t work at all. I can only use this measure as value (which displays nothing on the graph except 0, 0.1, .., 0.9, 1 on the y axe, and no bar) I can’t put the measure on the Legend Field for instance.

Unfortunately it is not possible to use a measure and put it on a label in Excel (but it is possible in Power View for non text meaures). So we need another solution. We can solve this in the exact opposite way. Lets look at an example using adventureworks with the FactInternetSales and DimProduct (filtered to only contain the current items) table:

I want to see the number of products per color per salesgroup where salesgroup is defined in Large, Medium and Small. The first idea that comes to mind is to create a measure that does what we want. That is pretty straightforward but unfortunately doesn’t work in Excel. So we need to come up with another solution.

What I did is define a table in Excel that defines the SalesGroup with Labels and min and max values:

Now I load this into PowerPivot as a linked table (no relationships created) and start by creating a pivottable using Color and the Label

Now I can create a measure that determines the number of products that fall between the min and max for each SalesGroup:

=if(HASONEVALUE(Categorygroup[Label]),
		COUNTROWS(
			FILTER(DimProduct,
				[Sum of SalesAmount] >= VALUES(Categorygroup[Low])
				&& [Sum of SalesAmount] < VALUES(Categorygroup[High])
				)
			)
	, COUNTROWS(DimProduct))

This gives us the result we wanted:

or create a chart from it:

Lets look at this measure step by step:

1. if(HASONEVALUE(categorygroup [Label]), … , COUNTROWS(DimProduct)): Do we have a single categorygroup selected in the pivottable ? If yes we can select a range, if more are selected return COUNTROWS(DimProduct)
2. COUNTROWS(: Count the number of rows
3. FILTER(DimProduct,  : From a filtered DimProducttable (observe that the filter created by putting colors on rows in effect as well)
4. [Sum of SalesAmount] >= values(categorygroup [Low])
   && [Sum of SalesAmount] < VALUES(categorygroup [High])) : Filter all rows in the DimProduct table where a measure [Sum of SalesAmount] is greater or equal then the value of categorygroup [Low] for the current categorygroup (using VALUES) and [Sum of SalesAmount] smaller then the value of categorygroup [High]

Hope this showed a simple approach to create a solution for Excel that gives you dynamic labels.

I had an interesting question today where someone send me a workbook that contained a flattened table like this:

Now as a result we want to see the top 1 nr of products per country. The problem here is that we cannot just load this data into PowerPivot and start analyzing to get this result. In this case the columns need to become attributes in a dimension. This is a classical ETL operation that can be done by many tools like SQL and SSIS , but not by PowerPivot as a modeling tool.

So how do you fix this problem ? At first I used a Macro that we found online to unpivot the data, that did get me the result I wanted. But some time later I realized that there is a trick in Excel that I can use that I have blogged about before here, by combining this trick and another it will be pretty easy to unpivot.

In this blog post I will show you how to Unpivot a flattened table using PowePivot and Excel (Actually you would not even need PowerPivot if you don’t want to).

The first thing we do is create a regular excel pivottable out of the table I showed in the first image

Next we change “Count of Product x” to “Sum of Product  x” and change the pivottable into a classic pivottable using Pivottable options:

This will turn it into a column per level:

Next we move the values from Colum to Rows, this results in an unpivoted result.

Next we need to turn the measure “Sum of Product X” into a column. Copy the entire pivottable and past it in a new sheet keeping only the values

This will result in a flat table inside Excel. Now I select the “Sum of Product X” table and use search and replace to replace “Sum of ” with a empty space. I also renamed the product column header:

Now the last thing that I need to do is get rid of the blanks and replace them with the same values as the previous row. Here I can use another Excel trick:

Start by selecting all the columns we want to fill the blanks of, go to the home tab and select “find and select”, and click “Go To special”. Here we can select
blanks and click ok. This will result in all the blanks being selected.  Type =, press the up arrow and press Ctrl+Enter (that’s hold down Ctrl and press the Enter key). All the
empty cells will be filled with the value of one row above. Exactly what we  wanted.

Now we can choose two options, load the table into PowerPivot or just use the regular Excel pivottable. I prefer getting my data into PowerPivot so let’s do that. Select a cell inside the table and go to the PowerPivot ribbon and click Create linked table. This will automaticly create a Excel table (make sure to select my table has headers) and load the data into PowerPivot:

Now we can create a new pivottable based on this table:

As final step we need to determine the Top 1 product per country, again we are going to use an Excel function. Select a product in the pivottable and click on the filter button on row labels. Now we select value filters, click Top 10 and change the number of items from 10 to 1 and press Ok.

This will give us the result we want:

As you can a lot can be done with Excel, you just have to know your way around. While this might not be a surprise for most of the Excel pro’s out there, we BI Pro’s are eager to run things through SQL or SSIS but it can just as easily be done by Excel. Maybe now I will be Excel gansta enough to join Rob Collie’s elite Excel group

I just couldn’t resist sharing this (from GeekWire)

This might be as close as we’re going to get to a time machine. Unless they’re working on that, too.

Microsoft this morning is premiering a new video that shows how the company believes technology is poised to evolve over the next five to 10 years, based on the trends its researchers and engineers are seeing in software, devices, displays, sensors, processors and intelligent systems

just watch it and tell me if you see the same things I did:

Yes it is the future according to Microsoft (as a whole not just the AS team ) and as you can see it contains a lot of BI in all shapes and forms. BI will be really immersive in every decision we make. And of course someone has to build the models and tools

One of the great things about a conference is the ability to meet so many of the great people that I have met on twitter or at other conferences and catch up and do all the great nerdy BI talk .

I will be working at SQLPASS  and you can find me at the following places / times:

Exportpod:

Thursday October 13th:  2 to 4 PM

Friday October 14th: Noon to 5 pm

And I also have two session:

Thursday October 13th: Building the Perfect BI Semantic Model for Crescent from 5pm to 6:15 pm

Friday October 14th: Enriching your BI Semantic Models with Data Analysis Expressions (DAX)  from 10:15 pm to 11:30pm

I would love to meet all of you and talk about whatever you want to talk about.

A great new demo of Crescent that I just wanted to share with you: