Search This Blog

Thursday, April 30, 2020

Customize Pivot Table Default Layout Style


When you create a PivotTable it defaults to certain views and if you create Pivot Tables often you probably adjust it a lot. Maybe you don't like the compact Pivot layout with it's nested view and prefer Tabular with repeated row values. Once the Pivot table is created you can manually change it by going into Options and changing it for each feature which this video will cover. That works if you don't create Pivot Tables that much, but if you do create them often and don't want to always click and select everytime just make those views the default. When you go into the Backstage view in Excel, just adjust the default settings and they will apply every time you create a Pivot and become your new Pivot Table defaults. Check out the video to learn how to customize this now.

Tuesday, April 28, 2020

Using the DAX CALCULATE Function


This video will cover using the DAX function called CALCULATE. Think of this function as a powerful SUMIF or COUNTIF function or maybe a "CALCULATE-IF" function. It will bring back some measure based on some criteria. In Power Pivot, it's useful in creating filtered conditions that bring some additional benefits to analyzing data. This video covers creating a simple relationship between two table and how the CALCULATE function can be used

Thursday, April 23, 2020

Multiple Keywords Search with Power Query


If you wanted to search for some keyword(s) you could use Ctrl+F (and select Find All) to do this. If there was one value in the column entries, a lookup might work (like VLOOKUP) or using the merge feature in Power Query, but if you needed to lookup a word in a string of text in the cell, those lookup feature don't work that way. If you had many keywords or a list of them, this makes it even more challenging. Also if you wanted to give this to your stakeholders and provide some interactivity and hide the steps for them to do this without using VBA or show functions in your worksheet, think about using Power Query to do this.