Implementing histograms in Excel 2013 using DAX query tables and PowerPivot

I got a great question last month on how to implement a histogram in PowerPivot, similar to this great blog post by Tomislav Piasevoli for Multi Dimensional. I decided to solve the problem in Excel 2013, PowerPivot and DAX and show you some of the great things Excel 2013 allows us to do.

First thing I did was import the data into Excel. As you might know you no longer need to separately install PowerPivot. Excel 2013 now by default contains our Tabular engine and the PowerPivot add-in when you install Excel. When you import data from sources to Excel they will be available in our Tabular engine.

I start by opening Excel 2013, go to the data tab and import from SQL Server:

I select the database I want to import from (same source as Tomislavs: Adventureworks) and select the tables I am interested in:

Key here is to select the checkbox “Enable selection of multiple tables”. As soon as you select that the tables are imported into the Tabular engine. Press Finish and the importing starts.

When the import is completed you can select what you want to do with the data, I selected pivottable:

Now I get the pivotable:

Ok now here starts the fun part . We now have two tables, one with all the resellers and all their sales. What we want to do now is know what the sales count of the resellers and have that available to pivot on. But in order for us to get this available to us we need to add this data to the model.

And this is where some new awesome features of Excel 2013 come into play. First I need to create a measure that counts the number of orders. For this I need the PowerPivot add-in. Make sure that you have this enabled, click on File, Options, Select Add-ins and Manage Com Add-ins. Press Go.

Now select PowerPivot and press ok:

Now notice that the PowerPivot tab is available in the ribbon, and click on Manage, to manage the model

This will open the PowerPivot window, now select the FactResellerSales table and create a new measure:

NRofOrders:=COUNTROWS(values(FactResellerSales[SalesOrderNumber]))+0

This will give us the Nr of orders based on the salesordernumber, observe the +0 to make sure it returns 0 when no rows are returned.

Now to make use of this measure I am going to create a new table based on a DAX query, this is a hidden feature in Excel 2013 but very very useful! Lets go back to Excel, select the data tab, click on Existing connections and select Tables:

Select either one of them, doesn’t really matter. Import the data as a table on a new sheet:

This will create a Excel table based on the data in our model:

Now this is where the fun starts, right mouse click on the table, click Table, Edit DAX (yes !)

Now a very limited dialog will be shown that has no syntax checking or formula help at all. But still an awesome feature for anyone who knows DAX. What this allows us to do is create a Excel table based on a DAX expression.

Now change the command type from Table to DAX and off you go:

In my case I created the following DAX Query:

evaluate
filter(
summarize(
DimReseller

,  DimReseller[ResellerKey]
, “nr orders”, FactResellerSales[NRofOrders])
, FactResellerSales[NRofOrders] >= 0)
order by  FactResellerSales[NRofOrders]

This query gives me all resellers with the number of orders where they have 0 or more sales.

I also made sure the table name was changes to “OrdersByReseller”. So how cool is that.. now we have a table in Excel that is based on a DAX query! One thing to note here is the change in behavior of Refresh inside Excel. Refresh in Excel will now automatically refresh the data as well, it will reach back to the underlying datasource and  add the data to the model. You should no longer need to press refresh to get the data of Excel to be in sync with the model. When you refresh the result of your DAX query will also be updated.

Ok, now we have this table but now we still don’t have a column with the number of orders that we can use as histogram. Well this is now really simple, we will push this table back to the model. Select the table, click on the PowerPivot tab and click Add to model. This will push the table back to the model:

And again this table will automatically be update as soon as new data comes in from the source.

Now I can create a pivottable to put nr orders as a Row:

Now in order to get the Reseller Orders Frequency I create three measures:

Reseller Orders Frequency:=COUNTA([ResellerKey])

All orders:=CALCULATE([Reseller Orders Frequency], ALL(OrdersByReseller))

Reseller Orders Frequency %:=[Reseller Orders Frequency]/[All orders]

I have hidden All order so it wouldn’t show up in the pivottable and set format to % on the “Reseller Orders Frequency %” measure.

And here we have it:

Pretty awesome right ? Just go back to the blog post from 2009 from Tomislav and compare all the work we needed to do there compared to this. This will open up so many new scenarios !

Have fun exploring Excel 2013 and PowerPivot.