## Power Query – Clean data for the Data Model

In previous exercises, we used the FILTER() function in order to remove the totals from our data model. Using Power Query, clean data is provided to the data model. Filtering before the data model improves the performance of the model, …

## INDEX / MATCH – How does it work?

Up until the release of the XLOOKUP function, there has been significant debate in the Excel community around which function is better – VLOOKUP, or INDEX / MATCH. But, what is INDEX / MATCH? This is a combination of 2 …

## ALL() and CALCULATE() – Percentage of a column

In the last post, we started using the CALCULATE() function to filter out some of the values that we didn’t want on our totals. This was done in order to get a pivot table that didn’t need to use the …

## FILTER and CALCULATE – Removing pre-calculated totals.

In the last post, we started using the CALCULATE() function to filter out some of the values that we didn’t want on our totals. This was done in order to get a pivot table that didn’t need to use the …

## Sum of a row using CALCULATE()

In the last post, we created a custom field in the Excel data model; however, we still needed to use Excel formulas to calculate the percentage of a row. In this example, we will be using a simple CALCULATE() function …

## Custom Field for Quarterly Data in the Data Model

As part of the Quarterly Report case study, we built a report using the data model, but there were still some instances where we used Excel formulas. In this post, we will be creating a custom field so that there …

## Quarterly Report – Using the Data Model

In this post, along prior two posts, I discussed a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles in Canada. This post …

## Quarterly Report – Pivot Table

In this post, along with the next few posts, I will be discussing a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles …

## Quarterly Report – Using SUMIFS()

In this post, along with the next few posts, I will be discussing a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles …

## Creating a Basic Loan Amortization Schedule Without Code

An amortization schedule contains details with respect to a loan, including the following: The principal amount of the loan. The interest on the loan (typically reflected in annual terms). The length of the loan (typically reflected in years; except in …