My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Excel PivotTables are fantastic for basic analysis, but they have limitations, particularly when working with large datasets or multiple data sources. If you've ever wished you could handle advanced calculations, work across multiple tables, or analyze millions of rows without performance issues, then Power Pivot is the solution you've been waiting for. In this post, …
Some might say that Excel CUBE functions are the new PivotTables because they allow us to build reports based on Power Pivot models in a more free-form fashion. Whereas PivotTables are quite restrictive in their layout and their cells cannot be edited. And just like PivotTables, we can use Slicers to filter CUBE functions for fully …
DAX is the Power Pivot formula language available in both Power BI and Excel. It stands for Data Analysis eXpression. As you can see in the images below, DAX functions are very similar to Excel functions, so it’s relatively easy for us Excel users to pick up. Below is a screenshot of how DAX formulas …
Power Pivot course member, Peter, asked if it was possible to toggle the top n with a Slicer for a Pivot Chart and the answer is yes! With Power Pivot and disconnected tables, we can create interactive charts like this: Watch the Video Download Workbook Enter your email address below to download the sample workbook. …
In this tutorial we’ll look at how to create a Power Pivot running total that allows you to see the aggregation at the month, quarter and year levels enabling you to create charts like this: Note: parts of this tutorial require Power Pivot in Excel or Power BI. Check if your version of Excel has …
Ordinarily when you want to create a table in Power Query, Power Pivot or PBI, you'd write a query to load it from an external source. But any time you have data that won't change (or changes rarely), you can use a static table. That is, a table that doesn't need a data source, it …
You may be familiar with Custom Format Strings in Excel, if you are not then check this Excel Custom Number Format Guide. What you might not know is that you can also use custom number formats in Power BI, and not only can you format columns of data, these custom formats can be applied to …
The GETPIVOTDATA function divides Excel users. You either love it or hate it, but there are good reasons for learning to love it. I wrote about using the GETPIVOTDATA function for regular PivotTables many years ago and hopefully you’re embracing it now. If you’re a Power Pivot user, then you may have found that the …
Occasionally you may want to show values as percentage of another PivotTable column total. For example, below we want to find what percentage the expenses for each region are of the total revenue: Unfortunately, you can’t do this with regular PivotTable calculated fields or PivotTable calculated items. However, we can with Power Pivot*, aka the …
With disconnected tables in Power Pivot* we can change PivotChart aggregation methods using Excel Slicers. It allows us to create Pivot Charts where the user can select different views of the data like so: It’s handy if you have limited space in your report or dashboard, as it allows you fit two charts into one. …
With the integration of Power Pivot into Excel 2013 (known as the Data Model), we now have access to a range of more advanced ‘Power’ tools right from within Excel. One of these tools enables us to easily get the distinct count for a field in a PivotTable. Note: Don’t worry if you don’t have …
If you use Excel PivotTables then you’re probably familiar with the need to consolidate data from multiple tables into a single table before you can Pivot it. One way to do this is using VLOOKUP or INDEX & MATCH formulas to bring columns from one table into another. However, in Excel 2010 onwards we have …
One of the most common questions I get is, what’s the difference between Power Query and Power Pivot and when should I use them. In this post I’m going to demystify these Excel tools and help you identify whether it’s worth your time to learn them and which one will give you the biggest benefit. …
Unfortunately there’s no built in way to calculate the median in a PivotTable. The approach is different depending on whether you’re using a regular PivotTable or a Power Pivot PivotTable. We’ll look at both options in this tutorial. The Data The data we’ll be using in this example is in an Excel Table called Table1: …
Excel PivotTable Named Sets enable you to pick and choose which columns or rows you want included in your PivotTable report, but there's a catch. They require you to use Power Pivot to create your PivotTable. Don't worry, it's so easy you won't even realise you're using Power Pivot. Note: Named Sets are only available …
