Kasper de Jonge PowerPivot Blog

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 had an interesting question on my ask a questions page, one that I have (or something similar) heard a few times before. So I decided to do make a blog post on how to solve it

Our company tracks changes in the resource planning using a data warehouse. An example would be:

Assignment 1: Ian 8 hours planned for 3-Jun; valid from 1-jan-2011 until 1-feb-2011.
Assignment 1: Ian 6 hours planned for 3-Jun; valid from 2-feb-2011 until 15-feb-2011.
Assignment 1: Ian 7 hours planned for 3-jun; valid from 16-feb-2011 until infinity.

I what to enable our end-user to select any date level (Date, Week, Month, year) and get the current planning for that planned date (3-jun).

First of all I created a table in Excel to represent the data:

Next I create a date table that I can use to select dates at different levels using a slicer:

I did not create a relationship between the two tables because I want to use the values of the date table to search for values in the planning table. So far i have prepared the tables, next is creating the report.

First I created a workbook and a new pivottable with the slicers from the date table and put the employee name on rows:

now for the interesting part, on to the DAX formula. We want to use the values from the slicers to determine the date we want to search the planning table on. We can use values(columnname) to get the current value of the slicer and create a date from that on the fly. For example values(‘Date’[year]) will get the current value of years from the slicer. IF you join all 3 together you can create a new Date: Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day]))

Slicers can have multiple values selected at the same time so we need to make sure that only one is selected at the same time, otherwhise we cannot create this date. We can do this with the following formula’s:

=if( countrows(values(‘Date’[year])) = 1 &&countrows(values(‘Date’[Month])) = 1 &&countrows(values(‘Date’[Day])) = 1,

Now on to creating the DAX formula to finding the right values in the planning table:

=if( countrows(values('Date'[year])) = 1
&&
countrows(values('Date'[Month])) = 1
&&
countrows(values('Date'[Day])) = 1,
CALCULATE(sum(Rates[Hours]),
	FILTER(Rates,
	Rates[Startdate] <= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day]))
	&&
	(Rates[Enddate] >= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day]))
	|| ISBLANK(Rates[Enddate]))
	)
	)
,Blank()
)

Let’s look at this step by step:

First we want to calculate the sum of Hours.

CALCULATE (sum(Rates[Hours]),

for the rows in the selected date range:

FILTER(Rates,
Rates[Startdate] <= Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day]))
&&
(Rates[Enddate] >= Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day])) || ISBLANK(Rates[Enddate])) )

Filter the Rates table to return only the rows where
the Rates[Startdate] <= the selected date
AND
Rates[Enddate] >=the selected date OR the Rates[Enddate] is empty.

When this formula is used we get the value we wanted based on the slicer we selected:

Make sure you turn the following “Visually indicate items with no data”  for each slicer (right mouse click, slicer settings):

Another blog post with a similar question and answer you can find here: http://www.powerpivotblog.nl/get-values-within-a-start-and-end-date-using-powerpivot-dax

Today the Microsoft’s Business Intelligence team released a very cool PowerPivot app that shows you the power of PowerPivot  from a direction you wouldn’t expect:

REDMOND, Wash. — April 27, 2011 — As tools, email and calendars go one of two ways for many people.

“Your calendar can be on top of you, or you can be on top of your calendar,” said Tom Casey, corporate vice president of Microsoft’s Business Platform Division.

To ensure he continues to operate in the latter group, Casey and his executive assistant, Melissa Krohn, are beta testers for a new business intelligence app called Calendar Analytics. The PowerPivot application retrieves meeting and other information from Exchange and presents it in a simple, easy-to-read dashboard they can use to analyze (and sometimes even change) how the executive uses his time compared to his priorities.

In honor of Administrative Professionals’ Day today, Microsoft’s Business Intelligence team is offering the application free along with an informational video to introduce the app.

For Casey and Krohn, the information analysis helps ensure day-to-day work is contributing to larger goals.

“At the end of the day, it’s helping him, and it’s helping me manage his calendar better, and making sure I am scheduling meetings that are going to align with his commitments,” Krohn said.

On a day that most people give administrative professionals flowers, today they can have “the magic of software,” said Bruno Aziza, director of Microsoft’s Business Intelligence.

In an email-and-calendar-driven world, having a tool for everyone from administrative professionals to information workers to executives to analyze how time is spent is invaluable, he said.

“We only have a set amount of hours available throughout the week,” Aziza said, adding that the biggest difference in competitive business environments can be how effectively people use their time.

 

So what happens here, a Excel add-in will download information from your Exchange (or Office 365) into Excel. This information will be uploaded into PowerPivot that feeds the Dashboards:

You can download the workbook for free from MS here. Happy Analyzing

Again a interesting question on my ask a questions page, keep them coming :)

Luke had a question where he wanted to get a date based calculation:

I have a table of people and a table of project assignments.
The assignments have a person, start date, end date and number of hours per week.

I want to create a pivot that has the date as columns and people as rows, with the total number of assigned hours as values.

I created a table in Excel that recreates this scenario:

Next I created a new table that contains a separate date range:

I loaded both tables  into PowerPivot. Because  I want to get the total number of hours per day I am unable to work with a relationship between the date table and the fact table. I need to create a formula that searches the fact table for those that rows are appropriate for each date value in the pivottable.

=Calculate(
	sum(Project[Hours]),
		FILTER(Project,
				COUNTROWS(FILTER(VALUES(Dates[Dates]),
						Project[startdate] <= Dates[Dates] &&
						Project[enddate] >= Dates[Dates] ))
				> 0)
		)

This measure does the following:
Calculate the sum of project[hours] for those rows in the Project table that has more than 0 rows in the Date table where Project[startdate] < = ‘Date’[Date] and Project[enddate] >= ‘Date’[Date].

The only downside of this query has to iterate through the entire project table for each row in the date table, is if the Project table is huge this might have big performance impact.

A second question was to create a project start date to current date sum. Again we need to create a measure to do it:

=if(COUNTROWS(VALUES(Dates[dates])) = 1,
	SUMX(DATESBETWEEN(Dates[dates], FIRSTDATE(Project[StartDate]), VALUES(Dates[dates]))
			, Project[SumPerDayMeasure])
, BLANK())

What happens here is that we want to sum all the values of the measure per day for each day from the beginning of the project to the current day.
Sumx will sum the values of our previous measure for each day between FIRSTDATE(Project[StartDate]) and VALUES(Dates[dates]), being the current day in the context.

Got an interesting question today. The question was about creating sparklines for all the country’s that shows a running total of sales for the last 12 months (Rolling 12 months) based on the year that I select from a slicer. I use the trick I blogged about before to work with sparklines and PowerPivot, I would recommend reading it for some background.

First I want to start by creating the sheet that I want to use as a report. On this sheet I start by creating the slicer that I want to use, In this case Calendaryear:

Next I create a second sheet where I want to collect my data for the sparklines.

Because we want to show the running 12 months per year I would like to use only the months of one year that is the baseline of the running month. So we hook up the slicer from our report to the pivottable in the other sheet. Select the slicer, go to options and click on pivottable connections, here I can select the pivottable from the other sheet to be filtered as well:

Next we want to create a measure that creates our running total:

=if(COUNTROWS(values(DimDate[CalendarYear])) = 1,
			CALCULATE(SUM(FactSales[SalesAmount]),
				DATESBETWEEN(DimDate[Datekey],
					DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)
					, LASTDATE(DimDate[Datekey])))
			,blank())

What this DAX measure does is first of all check if only one year is selected using:

COUNTROWS(values(DimDate[CalendarYear])) = 1,

If more than one year is selected we return blank(). If only one year is selected we calculate the running total by doing a sum of FactSales[SalesAmount] by creating a date range using DatesBetween.

The start date of the range is determined by getting the first day of the current month (we know this because of the current row context) and then go back 11 months before that:

DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)

to the last date of the current month:

LASTDATE(DimDate[Datekey])

Using this in combination of calculate the sum will now calculate over the entire date range.

Now we have the data set up we can create the sparklines. First we need to copy the rows and paste them to the report and create the sparklines on the report page, check the previous blog post on how to create the sparklines.

I have uploaded the workbook for download here.

Check out this great video from the Office team on what Excel and PowerPivot can do for you and your organisation:

You can find the original video here.

On of the benefits of moving to Office 2010 (besides of course getting access to PowerPivot ) is the existence of slicers. You can use slicers for all the Pivottable inside Excel 2010, not only PowerPivot but also a regular Pivottable on Excel data or based on SSAS Molap.

In this example I have connected to a Molap database and created a Pivottable to show the sales per Currency type:

Now I want this report to be dynamic per Year. We can use a slicer to slice the data per year.

Go to the ribbon, go to Pivottable tools, Options, Insert Slicer:

This will open a window that let’s you select the attribute you want to use in your slicer :

In our case we want to use CalendarYear. This will give us the following report on top of Molap:

Very cool

In Excel 2010 MS introduced Sparklines, as described on the Excel blog:

For Excel 2010 we’ve implemented sparklines, “intense, simple, word-sized graphics”, as their inventor Edward Tufte describes them in his book Beautiful Evidence.  Sparklines help bring meaning and context to numbers being reported and, unlike a chart, are meant to be embedded into what they are describing.

Too bad the sparklines aren’t really tightly integrated with PowerPivot  as are other charts. In this post i’ll describe an easy way to implement sparklines on PowerPivot data, with use of slicers.

Of course we start by loading data into PowerPivot, next  we create the following workbook in PowerPivot:

To gain more insight in sales per country per month I would like to use the sparklines per country. Lets start by adding data in a new sheet

Make sure you connect this Pivottable to the slicers from my other sheet by using the connect slicers option as posted in this blog post.

Ok next we are going to add the country’s to the original workbook. Select the country’s and copy them. Paste hem into the original sheet:

Now for the sparklines, go to insert and click Line. we now have to select the source for the sparklines, in our case this is the pivottable in our newly created workbook, and select all data in the months:

next we have to select where we want to put the sparklines:

This will result in the sparklines being showed:

We want them to be a little wider so we select the cell with the sparkline and the adjacent cell and click Merge and center to let our sparkline span two cells, next we select another sparkline design, I also like to use the markers.

This results in the following workbook:

We can even use the slicers, as we can see here when we select 2008 we only have 8 months, this will show in the sparklines as well:

I love sparlines, and they aren’t that hard to implement, for more information on sparklines see:

http://blogs.msdn.com/excel/archive/2009/07/22/formatting-sparklines.aspx and http://www.msofficegurus.com/post/Excel-2010-Sparklines.aspx