Excel Tips
March 28, 2023
With SUMIFS, you go through a data set, finding rows that match all of the criteria. With CALCULATE, you go through a data set, calculating values that match the filters in CALCULATE.
Calculate() is Like SUMIFS() »
March 27, 2023
As you get started with DAX, you are going to find yourself using the CALCULATE function. This function will perform a calculation while applying any number of filters. =CALCULATE(Sum(Field),Filter1, Filter2, Filter3).
Replace Calculated Fields with DAX »
March 24, 2023
The DAX formula language really shines when you use it to create a new measure for your pivot table. DAX measures are in the same genre as calculated fields, but are infinitely more powerful.
Feature X Won’t Work in Power Pivot »
March 23, 2023
This is not a regular pivot cache pivot table that you’ve been using for the last 10 years. That data in your Excel worksheets is now in the Data Model. Even though the Data Model is stored inside of your Excel file, it gets treated like external data.
Creating the Power Pivot Table »
March 21, 2023
The Pivot Table dropdown offers 8 choices and most of them are fairly silly. PivotTable and Pivot Charts are obviously needed. Pivot table pros will love the Flattened PivotTable. But all of those choices in the middle are redundant.
Refer to a Related Table in a Formula »
March 20, 2023
I am entering a formula in the Fact table. I need to lookup a value from Product table. I’ve already defined a relationship between the tables.
Adding Calculations In the Power Pivot Grid »
March 17, 2023
Power Pivot introduces a new formula language called Data Analysis eXpressions or DAX. When you see a Power Pivot demo, the 2 million rows and the joining tables look impressive. But it turns out that DAX is the really jewel in Power Pivot.
March 15, 2023
One downside of Power Pivot is the inability to group daily dates up to months and years. The common workaround is to build a lookup table that contains every daily date from the earliest date to the latest date in your data.
Sort Month Name by Month Number »
March 14, 2023
Regular pivot tables use the Custom Lists dialog to automatically sort months into Jan, Feb, Mar sequence. Power Pivot doesn’t seem to be aware of Custom Lists and sorts into the alphabetic sequence of Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar. May, Nov, Oct, Sep.