For both of our power pivot reports, so far, we've relied strictly on excels, pivot table magic to
construct our calculations for us.
We haven't touched a single formula so far, which means that we haven't even begun to scratch the surface
of what we can do before we write any formulas.
You need to understand what types of formulas we will be writing.
The language is called tax, which is shorthand for data analysis expression's.
However, there are two distinct types of outputs that can come from our tax formulas.
The first and simplest type is a calculated column.
You can think about a calculated column as being some permutation of items within a specific row to
generate a single output.
Let's look at some Excel formulas to understand what I mean.
This is our employee wages table.
Now imagine if we wanted to calculate the monthly wage paid to each employee on a TRO.
We would take the employee wages and we divide that by the employee count so that that new column would
contain the information describing the average wages.
Most calculated columns are like this.
You have some series of inputs and you want to generate a singular output that is specific to the row
in the table.
Calculated columns are in effect added to the table so that you can think of them as being part of the
original data set in terms of both storage and file impact.
Now, while I've described this as a one for one calculation, in a sense, that doesn't necessarily
mean that it can't include multiple sources of data.
So, for example, I could create a new calculated column that is the sum of another column.
In this case, it's the sum of the entire column.
I can also create one that's a conditional sum using some ifs.
This is pretty common in Excel, as if I wanted to calculate the percentage of the year's total that
each month contributes, while this is technically a calculated column.
It could be better handled the power of it through what are called measures.
So why is that?
If we look at our calculated column, it's not sensical.
Once we start aggregating rows together, imagine dragging this annual total column into your pivot
table.
You'll get the actual annual total times some number because all of them will get added together.
The value is incongruent with the row that it's in.
That is the data in the cell.
Is that a different type of granularity than the data in this particular row?
And so the calculation only makes sense under a specific context and view.
Outside of aggregations, all of the values around it can be added, averaged, counted or operated
on in other ways to get meaningful pieces of information.
So it is very clearly out of sync.
So this brings us to the concept of measures.
Think of measures as standing separate from your data, they are named measures because they are, in
effect measurements of your data, whether that is a total and average account or a more convoluted
calculation that can merge one or more values together.
So, for example, for the sub, we could have specified a smaller table to the side that included just
the entire sum, or we could have had a pair of summary tables that include contingent subs.
But for all of these, we can think of them as the measured sum for the rows that filter down to that
subsection.
In effect, we can simply refer to this as a sum.
Instead of thinking about this as a specific value, we could think about it and save it as a specific
formula.
Just give me the sum for any values from this column that show up in my filter.
In terms of Excel, you should think about it more like the subtotal function which doesn't total hidden
rows and will only give you a aggregation for the rows that are visible to you to demonstrate.
Let's add a subtotal function here.
The subtotal update based upon whatever filter I have applied to the table, it is separate from the
concept of the table, but still reliant on and connected to the table for its results.
So it will update based upon its own context.
That is, which rows are visible to it.
This concept could be difficult to grasp, as there are many cases where it could be challenging to
determine whether a calculation should be done with calculated columns or with measures.
So here's a quick review, along with my thought process differentiating between a calculated column
and a measure, a calculated column adds data to a table.
The column should be at the same granularity as other entries in the table.
Measures, on the other hand, are formulaic computations that should be thought of as merging one or
more rows of data together.
My rule of thumb is that if a computation requires more than one row of data to calculate at any point
in time, then it it is a measure.
Otherwise it is a calculated call.
Now, though, we know what they are.
Let's build some in our next lesson as I introduce you to DAX.