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.
Really nice post and features provided in Excel 2013 Kasper!
Thanks for the mention btw.
Nice
but you can do it also in excel 2010
Hi Dan,
No this is a native Excel feature that is new in Excel 2013 so you cannot do it in Excel 2010.
Kasper
What is the maximum number of number of rows that Excel 2013 supports?
Thanks.
HI Steve,
If you use the Data model you can add as much rows as fit in your memory.
Thanks,
Kasper
Wouldn’t you be able to do something similar with the PowerPivot add-in installed in an Office 2010 suite?
@Kasper de Jonge
Right-click on table cell, then select Table | Edit Query (in Excel 2010).
I noticed there is recommended charts in the 2013 version and did see a histogram type graph, though having trouble getting it to suggest it again. Is there an easier way, I didn’t go through as much processes as you’ve highlighted when using 2010 excel.
Hi Kasper,
cool stuff.
Thank you for sharing.
Wouldn’t it be easier with a calculated column NRofOrders ? IMO that is actually what you do indirectly.
better late than never! just finished working through this example. as usual, thanks for your generous sharing
Data profiling for the masses 🙂
This is amazing. Thanks for sharing. I’m using it to calculate values for control charts where we actually want averages of averages. I couldn’t figure out another way to handle that solely within the model.
Hi, the table I am trying to run the histogram on has 13 million records. Therefore I am unable to export this data as a table to excel in order to create the DAX expression off it. Is there a workaround to this?
Hi, I tried to access the “Edit Dax” feature but it is unavailable (in grey). How to make it available?