Working with Power Pivot it isn’t hard. In fact it’s easier than Excel but the logic is totally different. But if you respect the next 3 rules, you will be able to build measures for your model
Rule 1: Structure of the data
When you want to create a measure, your data must be stored like in a database ; all the same data in only 1 column.
For instance, here is 2 presentation of the same data.
Even if the first presentation looks better because you can read easily your data, the second is the only one to present your data if you want to use Power Pivot.
With the second presentation, all the data for the calculation are in only one column and not 12 compared to the first presentation.
Rule 2 : Basic formulas are the best
When you work with Excel, COUNTIFS and SUMIFS are common functions to calculate with criteria. But when you work with Power Pivot, you must think more simple.
When you ask to an agregator to calculate your data, it’s the nature of the calculation that is the most important like SUM, COUNT, MIN, MAX, AVERAGE, …
In fact, this principle is already visible in Excel when you change the type of calculation in a Pivot Table.
Rule 3: Filters starts the measures
With Power Pivot, the name of your formulas is a measure.
Once your have created your measures, you just have to simply drag it into a field in a pivot table to visualize the result.
Here is an example of the behavior of a measure in a pivot table. Each time you select a value in a slicer, this action give the order to the measure to be recalculated.