Use slicers to select the facts in a pivot chart

In one of my previous posts link – I described how you can create a dynamic pivot chart in Excel 2013 – but it did require VBA to change the fact.

But inspired by Rob Collie – link@powerpivotpro – and several readings about the DAX SWITCH function – I decided to see if I could use slicers to create a dynamic chart using Slicers.

So first I created a power pivot model based on Adventure Works DW, and created some facts

Sales Value

Sales Value LY

Sales Units

Sales Units LY

Average Price

And the user should now be able to select from a slicer which of these should be plotted in a development chart.

So I start by creating a table in Excel with the facts

Adds the table to the data model in the workbook

And create a measure called

Selected fact:=min(Table1[FactID])

This will be used in another fact which is the one I will use in the pivottable/chart.

So when I create a pivottable the Selected fact will return the ID of the fact when the Fact names are placed on rows or columns.


Then I create a dynamic fact calculation

Fact to plot:=SWITCH([Selected fact];1;[Sales Amount];2;[Sales Amout LY];3;[Sales Units];4;[Sales Units LY];5;[Average Price])

So adding the fact to the pivottable it looks like this – with the correct value under each fact – DAX magic πŸ™‚

You will get a warning about missing relationship but you can ignore this.

So Insert a slicer with the Fact as the slicer


And now I can filter the table with the slicer


And now you can create a pivot chart based on the pivot table and add a slicer for the year and other dimensions if needed..


OBS – you cannot use this technique with the new pivotchart in Excel 2013 but you have to have a pivottable behind the chart – but you can put that on a hidden sheet if needed.

You can download the example file from here – Download

Analyzing OSA (On-Shelf Availability) with Excel 2013 and Power View

OSA or OOS (Out of Shelf) is a huge issue for retailers and their suppliers. However identifying the stores or products where the OSA is a problem has required a lot of visits to the stores either by suppliers field force or by using crowd sourcing agents – like Gigwalk, Jobwalk, Fieldagent or similar.

However if you have access to POS data from the retailer – Store by Store – you can analyze the issues by using Excel 2013 and Power View.

By creating a PowerPivot model on the POS data and create measures to calculate average sales and identify the weeks with low or zero Sales, you will now be able to track, analyze, filter and last but not least plot it on a map using Power View.

In this example there is more than 1 million records and within 2-3 seconds I am able to visualize the lost Sales by Category and Store within a region using Power View in Excel.

I knew that PowerPivot was a powerful tool – but this is amazing.

At a later point I hope to find time to create an in-depth example but just had to share my excitement about this.

Analyze the size of your PowerPivot workbook model – introducing PowerPivot Examiner

Inspired by the MS release of Workbook optimizer link and Kasper de Jonge’s post “What’s eating up my memory Powerpivot – Excel Edition” – Link – I actually found myself missing a tool that could tell me why my PowerPivot workbook was so big even though I had used these two tools.

One thing that really frustrated me was the fact that Office365 tells me that I can upload and browse my Powerpivot workbooks … but the maximum size for these files are 10 mb 😦 and it’s very hard to have millions of rows and stay below this limit.

So I decided to find out what is taking up space in my model.

Quick guide

  1. Open a workbook with a Powerpivot data model
  2. Open “PowerPivot Examiner” –
  3. Click the “Choose File” button and select the workbook opened under 1

  4. Click OK and the table below will be updated and so will the chart

Β 

The table is linked to PowerPivot and is then visualized in the pivot chart. And because we are in Excel 2013 – you can now drill up and down in the pivot chart.

So when I double click the largest table I can see which columns in the FactSales is taking up the space

Β 

Reading the chart I can see that I have no chance with the current number of rows in my fact table I have no chance of getting below the 10 mb magic limit.

But I can also see that if I remove the Totalcost and UnitPrice I can reduce my file size from 36 mb with appx 9 mb to 27 mb – and there by optimize the load time and thes speed of the model.

Feedback

If you have comments or suggestions to the tool please don’t hesitate to contact me via the comments.

Download

Here is a link to the model – Link

Β 

How to use Data Explorer for Excel to extract data from Statistics Denmark

StatBank Denmark contains detailed statistical information on the Danish society, and they are exposing a lot of interesting statistics about Denmark.

Unfortunately they do not expose these data in oData feeds, public web services but only via an interface where you manually export the tables in excel, csv, html or similar formats. But then I noticed the other day that you have the possibility to access the your saved tables as XML and as Microsoft “Data Explorer” Preview for Excel handles this perfectly – I thought that I would give it and try so let me take you through the process.

Step 1 – Log on to Stat bank Denmark

To select the data you need to logon to StatBank Data – http://www.statbank.dk/statbank5a/default.asp?w=1920 and you should create a use account so you can extract large tables and most important – you can save your queries and that is important.

Now you have access to all kind of interesting things about Denmark.

Step 2 – Create a query

Let try and analyze the “Live births by age of mother and sex of child”.

I choose all the elements in the different available filters and click Show table. The result is this table

And if I choose to export this to CSV or Excel I get a table that is not very useful for further analysis.

Step 3 Save the query

At the bottom of the page you can select to save your table for future use and also specify whether the timeperiods should rolling, fixed or only latest updated periods.

Give the report a name and click “Add as new saved table”.

Then you will get a id for the table in the next picture and this ID is important for that will be used to generate the xml file later on.

You can also find the ID in the link info at the status bar.

Step 4 – Test the XML feed

Now can get the XML via calling

http://www.statistikbanken.dk/xml/167000

Step 5 Get the data into Excel

So choose Import XML from the Data Explorer tab and enter the http://www.statistikbanken.dk/xml/167000

This takes 10 – 15 seconds for the file to be loaded and data explorer to appear.

Then you can click the Data – in the navigator and see the table data.

Now we can format and rename the columns.

So final result

Click Done and the data will be returned to Excel

Step 6 – Load the data to Power Pivot

In Query Setting Task pane – Select not to Load to worksheet and click load to data model

And now I can refresh data directly from PowerPivot

Step 7 Analyze the data

And now that you have the data in Power Pivot you can start to analyze and visualize the data using Power View or just the standard Excel charting tool

Or

Step 8 – More data

And now you can import other relevant data from Statbank to see what might have an impact on the birth rates.

You try

You can find the example file here – and the cool thing is that you can use the same query that I have created in my user profile on Statbank.

Links

Download Data Explorer

Stat bank Denmark

The xml data

Using Data explorer and PowerPivot to analyze your hard drive

Microsoft has released a preview version of “Data explorer” for Excel – link and it contains a lot possibilities for enhancing the self-service BI experience.

One of the features is the possibility to Import from folder that when followed “normally” can combine/merge multiple files into one table.

Β 

Then you can specify the folder

The first step will then give you a list of the files in the folder but also the subfolders in the specified folder.

Then I added the size of the file by clicking the “Attribute” header you can pick from the different file attributes.

Β 

Then I clicked “Done” icon and when returned to Excel I chose to load the data in Excel but also to add it the workbook data model.

Β 

In PowerPivot – I then added calculated columns to calculate the Year, Mont, Day, Weekday and Hour of creation to be able to analyse the data.

And then it fairly simply to create and overview of when I actually created data on my C – drive

Β 

This can also be used to analyze folders on your network drives.

Really look forward to examine all the exciting possibilities in Data Explorer – looks very promising.

Β 

Β 

Β 

Β 

Β 

Β 

Β 

How to insert Slicers from PowerPivot hierarchies the right way

I think the Excel 2010 introduction of Slicers to filter your pivot tables makes it so much easier to filter the information in the pivot table and the ability to let slicers “Show items with no data last” was very helpful.

But when working on demo based on Adventure Works I was very surprised that it didn’t work when I added the Geography hierarchy as slicers to my Pivot table.

In this example I choose Canada as the Country Region and the state province slicer shows me the Canadian provinces but not as the first items … that’s still the Australian provinces 😦

So naturally I checked the setting of the State Province and it was apparently correct.

After trying a couple of times more using the “Insert Slicer” from the “Pivottable” tools table…

And thinking about MDX and how you must break hierarchies in MDX function DESCENDANTS to sort by using the BASC or BDESC – I thought it would be an idea to add the individual levels in the hierarchy as filters instead.

And voila – now the slicers “works”

So in order to make slicers from hierarchies as you would hope for – remember to select the level individually instead of from the hierarchy.

Create a dynamic table from powerpivot data in Excel 2013 without vba

Inspired by Chris Web’s blog post about Dynamic DAX Query Tables in ExcelΒ 2013 – link – I have tried to create a dynamic table from PowerPivot data without using VBA so it would be possible to publish it on SharePoint and browse the model using Excel services.

So how could we do this – let’s start by adding some data from the AdventureWorksDW to a data model in the workbook.

We only need to create the connection and this will add the data from the database to the PowerPivot model.

The relationships will be created automatically in this case, and I add a measure to the FactResellerSales – Sales Amount:=SUM(FactResellerSales)

By using the CUBESET function we can create a set using an MDX expression, this will return a set containing all the members of the column ResellerName in the table DimReseller.

The CUBESETCOUNT function can calculate how many children there is in the set.

Be aware that when you look at the table in the PowerPivot window we can see that there actually is 701 members – This is because the Reseller names aren’t unique. In order to avoid this we could use the ResellerKey instead but in this example we ignore this but be aware of this you choose to use this method.

Let’s create a table.

So I created a simple table with a reader and add the first row a formula that uses CUBERANKEDMEMBER to retrieve the first member of the set of Resellers.

To make the rank more dynamic – I calculate the rank by using the Current ROW of ResellerName minus the Row number of the Header row

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”;$C$4;ROW([@Resellername])-ROW(Table1[#Headers]))

So in row 10 – the rank argument in the function will be 10 – 9 = 1

In order to make the table a list of resellers sorted after Sales Amount – I modify the formula in C4 to

=CUBESET(“ThisWorkbookDataModel”;”ORDER([DimReseller].[ResellerName].Children, [Measures].[Sales Amount],DESC)”;”The Set”)

To add other columns from the reseller table I add a header containing the Field name and add the following formula in the column.

=CUBEMEMBER(“ThisWorkbookDataModel”;”FILTER([DimReseller].[“&E$9&”].Children,([DimReseller].[ResellerName].[“&[@Resellername]&”], [Sales Amount])).Item(0)”)

By specifying the MDX expression “FILTER([DimReseller].[“&E$9&”].Children,([DimReseller].[ResellerName].[“&[@Resellername]&”], [Sales Amount])).Item(0)” only one member will be returned and will be the field specified in the header.

This formula can then be copied to other columns and by entering a valid column header the corresponding field will be retrieved from the data model.

To finish the dynamic table you can copy the formula down as many rows as you want to retrieve and add a slicer to the table – the cool new Excel 2013 feature.

Here is a link to the example – http://sdrv.ms/154zlUi

 

Using Cube functions and MDX to find the last period with sales

The Excel cube functions in combination with PowerPivot is very useful when you design dashboards in Excel.

The Cube functions enables you to retrieve sets, members and values from the PowerPivot model and you can use your knowledge about MDX to make them even more powerful.

In this post I will demonstrate how to find the last month with sales and then use that member to create a sparkline or chart to show the last 12 months of sales.

The example is built on the AdventureWorkDW2012 sample database from CodePlex.

I have built a Year month hierarchy based on the calendar year and a month label (Calendar year + month number)

First I use the CUBESET function to calculate all month with sales.

=CUBESET(“ThisWorkbookDataModel”;”FILTER([DimDate].[CYM].[Month label].MEMBERS,[Measures].[Sum of SalesAmount])”;”Periods with sales”)

The first argument refers fo the powerpivot model (remember the new name in Excel 2013). The second argument is a MDX expression that filters all the members from the [Month label] members in the Year month hierarchy. The third argument is not needed but handy to show give the cell a visible feedback.

Then I use CUBESETCOUNT function to calculate the number of month with sales

=CUBESETCOUNT(H7)

This will return 36 which is the number of month with sales.

To retrieve the last month with sales I then use the CUBERANKEDMEMBER

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”;”FILTER([DimDate].[CYM].[Month label].Members, [Measures].[Sum of SalesAmount])”;H9)

This will return the last member ie the last month of sales as the third argument refers to the CUBESETCOUNT result.

Β 

To create a dynamic area with the last 12 month of sales you can build a table like this

Β 

The formula to retrieve the sales value for the month can then be calculated like this

=CUBEMEMBER(“ThisWorkbookDataModel”;”[DimDate].[CYM].[“&$H$11&”].lag(“&T16&”)”)

Where the lag expression can retrieve period – x periods compared to the last sales period.

And then retrieve the Sales Value by referring to the members and slicer if you want.

=CUBEVALUE(“ThisWorkbookDataModel”;T17;$H$18;Slicer_SalesTerritoryCountry)

And remember that you can refer to Measures as Members so you can make your dashboard very flexible.

You can download the example file from here

How to get selected items in a slicer without VBA

Slicers is an essential tool in Excel when working with power pivot pivot tables (and “normal/old” pivot tables). But when connecting the same slicers to multiple pivot tables and perhaps place all your slicers on a separate sheet you have to be able to show what your model users have selected in the slicers when they look at the pivot table.

You can create VBA code to read the selected items from the slicer object, but this code cannot be run if you publish your model for use in Excel Web App on SharePoint.

So to avoid code I have created formula to generate a text string indicated what the user selected in a slicer.

Step 1 Create the data for a powerpivot model

Β 

First I create some sample data to use in a powerpivot model.

The Store =INT(RANDBETWEEN(1;50))

The date =TODAY()-RANDBETWEEN(1;100)

The Product =RANDBETWEEN(1;10)

The Sales Value =RANDBETWEEN(100;4000)

And then copy these formulas to generate a random number of rows.

Step 2 Create a linked table to PowerPivot

Β 

Select PowerPivot and click Create Linked Table

This will link the data to PowerPivot so we can build a simple model.

In order to create a year and month slicer – I add to calculated columns.

Year: =Year([Date])

Month: =Month[Date])

Step 3 Create a pivottable with slicers

Β 

Then I setup a pivot table with slicers that might look like this.

Β 

Step 4 Let’s calculate the selected items in the Store Slicer

Β 

A slicer in Excel has some settings in which you can identify the slicer in your formulas.

Β 

In this case our slicer “Store” is called “Slicer_Store”.

We can use this name in our formulas.

In order to get the number of selected items in a slicer we use the function – CUBESETCOUNT

Β 

The formula =CUBESETCOUNT(Slicer_Store) will return 1 – as all items is selected

Β 

Β 

When you select three items it will return three.

Next function to use is CUBERANKEDMEMBER-

Β 

CUBERANKEDMEMBER can return a member in a set by specifying it rank in the set.

Using the name of the slicer – the name will return a set of selected items in slicer !!!!!!!!

=CUBERANKEDMEMBER(“PowerPivot Data”;Slicer_Store;1)

So this formula will return the first selected item in the slicer and if all items is selected it will return All.

Β 

Step 5 Lets create a label for the selected items

Β 

A slicer can contain many items and I don’t want to create a very long label, I chose to let the label contain the first 10 selected items and if more than 10 is selected – the label should return the text “More than 10 selected”

Β 

In column M there is a rank number from 1 to 10.

In column N next to the rank I create a formula that retrieves the ranked member in the slicer.

=IF(M14<=$N$12;CUBERANKEDMEMBER(“PowerPivot Data”;Slicer_Store;M14);””)

The formula test if the rank is lower or equal to the number of selected items in the slicer (calculated with CUBESETCOUNT), if it is – it will return the ranked member in the Set – otherwise it will return blank text.

In column O I create formulas to generate the label.

First formula just takes the first member

=N14

Second item

=O14&IF(N15<>””;”, “&N15;””)

Tests whether the N columns returns an result – if it does it will concatenate the two labels split by a comma.

This formula is copied down for all rank numbers.

And finally a formula to create the complete list or if more than 10 is selected

=IF(N12>10;”More than 10 selected”;O23)

The cell is then named “StoreSelected” and then you can refer via the name on any of your sheets in the workbook.

Try it

Β 

You can download the example here – Link (open the file in Excel – not in Excel web app as powerpivot isn’t supported )