BIDA0033 – New Design Techniques For Excel Power Pivot


Hello and welcome to our latest blog post.

In this blog post we just wanted to mention upgrades we have made to the development methods we are using for our Excel Dashboards.

During this year we have released detailed training material on how we are building Excel Dashboards.

You can watch these videos on our web site.

Over the last few weeks we have put in some time and effort to see if we can find even better ways to design and build these Excel Dashboards.

We have managed to make two substantial technical upgrades.

These two upgrades do not make any difference to the users experience of the Excel Dashboard.

They make Excel Dashboards easier to design, easier to support, and faster to produce, on a daily basis.

The two upgrades are as follows.


We have converted the “ExcelTables” workbook to DAX.

The ExcelTables workbook was created to be able to update charts based on slicer clicks even if the number of bars or data points on the charts changed.

So, for example.

If you clicked on a slicer for a product category and that product category was not purchased by all age groups, then the charts for age groups would adjust the number of bars that are displayed.

The ExcelTables worksheet is quite complicated and we have long thought that DAX might be the answer to those complications.

We have now developed DAX calculations to replace this worksheet.

The DAX calculations make the worksheets easier to create.


One of the features we wanted to embed in our workbooks was to only have one copy of the data inside the power pivot model.

In our first release of the dashboards we achieved this by having a separate “data” workbook.

This separate data workbook was linked to the dashboard workbook.

In preparing the dashboard workbook in Meta5 the data was read from the data workbook into the power pivot model in the dashboard workbook.

This is quite a slow running process.

It also had the disadvantage that the location of the data workbook must be hard coded in the dashboard workbook.

You can see in the demos we had to create a folder on the C drive and we had to give it a standard name so the dashboard workbooks could know where to look for their data.

We have now developed a way of sending the data into the Dashboard Workbook in Excel Tables.

These Excel tables are then read into the power pivot model.

All the charts and reports are updated from the power pivot model.

The data is then deleted from the Excel Tables.

This then emulates our current feature that the data is only in the workbook once, and only in the power pivot model.

However, we do not need a second “data” workbook to achieve this result any more.

Obviously this means our training materials are a little out of date with our new best practice.

We wanted to mention this on our web site publicly.

Just so you know.

It took more than 100 hours of work to create our internal training materials and our public training materials.

Because these videos take so long to create, it will be a little while before we release updates to our training materials.

In thinking about Meta5 + Excel the bottom line to keep in mind is this.

If you love Excel and you want to use Excel for your reporting and your dashboards?

You can get any data, from anywhere, in to Excel using Meta5.

You can put that data into Excel regions, tables, or the power pivot models.

You can parameterize the Excel Dashboard production so that each person receives spreadsheets containing only the data they need for their reports.

You can deliver the Excel Dashboards to any device by any mechanism you have available to you.

You can even render portions of the reports as PDFs.

We love Excel.

We use it every day.

Microsoft has announced there are 300 million in force subscriptions for Office 365.

There are 300 million people out there who have Excel available to them, already paid for, and many of them would be happy to get their Dashboards in Excel.

If you love Excel and want your Dashboards in Excel?

Meta5 will make your Excel Dashboard development, support, and production running, faster, cheaper and better.

We can guarantee this.

We can put the data you need into your Excel Dashboards for you to create the best Dashboards possible in Excel.

And if you would like our people to build your Dashboards for you?

We are training them via such well known teachers as Minda Treacy and Other Levels Dashboards.

Thank you very much for your time and attention!

We really appreciate you reading our blog post!

We wish you a great day!

Best Regards


We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.


Please enter your comment!
Please enter your name here