As part of structuring this course, this practice exercise, I thought one last video running through
some slightly more advanced measures might be in order.
The first thing we'll do in this lesson is we will improve our key performance indicators by making
it compare our cash on hand against the expenses from the previous month.
This will standardize our expense level at a specific rate rather than letting it expand and contract
with our other measures and contexts.
This will give us a more consistent view of how our cash on hand position is relative to expenses.
The second thing we'll do is we will create a more advanced profit measure that will mix and allocate
our labor costs across each of our departments.
If you remember from the custom hierarchy lesson, we couldn't drill down past the general level for
any of our profit computations since our wages and expenses did not line up.
So let's start with our key performance indicators to do this, I'm going to create a new measure and
we're going to call it the prior month expenses.
I'm going to follow a similar pattern to what I use for the cash on hand.
So similar that I'm going to copy its formula and use it as the base.
I'll replace profit with total expenses.
And in the filter area, I want to adjust our filter so that the list of rows from our calendar table
is any date from the prior month.
This may seem to be a bit more challenging to envision than it seems.
I'm going to keep the all calendar peace, but now I want to restructure the following filter component.
I still want the dates that are before our current maximum date, but instead of strictly less than
our maximum date, I want dates that are at least a month before that date.
Your first thought might be to adjust that maximum calendar date by using the date add function and
subtracting a month.
However, the date and function works with arrays of data, so that leaves our calendar date array for
us to work with and so we can't subtract a month from our maximum date.
What I can do is add a month to the calendar dates themselves, so I'll use the date, add function,
add it to the front, put a comma after the calendar date, use one for one month and then I'll use
a month as the iterator.
So this'll tell my measure to filter out any records that are aren't at least one month old.
If that's still not quite what I need.
I need records that are only one month old, not records that are older than one month.
So I need an end condition here.
I'll use the end function and pardon me while I make a bit of formatting changes here to help control
the flow.
And let's think about this next step.
I can use the same concept as I did with the data add here, so I'll copy the line and paste it.
If I want to include only those dates in the prior month, then adding two months to those dates beyond
that would generate a date greater than my current maximum date.
So that's the approach I'll take.
I'll replace the one with the two and the less than or equal to sign with a greater than sine.
I'll do a check of the princes click on my check formula.
And I'll press, OK, so let's go see this in action, my cash on hand goal is my total expenses field,
so let's filter to a month view and then add my prior month expenses.
When I do this, I can see that my prior month expenses line up with the total expenses from the prior
month.
They're all like by month and so it looks great.
So let's go adjust our KPI.
Simply changing total expenses to prior month expenses is a solid change.
However, I'll also change the targets to 15 percent and 30 percent.
When I close out of this, we can look at our cash on hand status and see an indicator that is more
useful than before.
That was nice and quick and easy.
Our next task is slightly less so.
For this next step, it's not strictly necessary you could stick to aggregating your data up so that
you're not making any data adjustments and simply hide the department drilldown since it provides no
current value.
But what if we really wanted to have some type of allocation across the different department groups
to see profit computations on a per department basis?
We know that butcher and bakery are losing money, but where are our profit centers?
Step one is to conceptualize what we want to do.
I'm going to assume that each unit of quantity takes an equivalent amount of labor to manage.
So I'm going to use the quantity details to allocate labor.
That means that if one department makes up 10 percent of the quantity of goods, then I want that department
to get 10 percent of the labor expenses.
So how does this translate into our measures?
So what we want for our measure then is something like this.
We want the quantity of the specific department divided by the quantity of all of those departments,
multiplied by the wages from all of those departments.
That sounds pretty complex, but we've already made measures similar to each piece of this puzzle.
While we could combine all of this into one measure, I'm going to break it up into multiples, one
for the quantity of the specific department, one for the quantity of the sections and then one for
the wages of the section are final adjustment will be to the wages total measure, which will use all
three of those previous measures together.
So first up is the quantity for the specific department.
That's just going to be the sum of quantity for whatever aren't given.
Context is we've already defined this with a quantity total next as our quantity by section, I'm going
to create a new measure for this in quantity total section.
Think about how we did our cumulative total.
We used calculate and then we had a filter that expanded what rose we included, which will bring us
to a new all function.
The all except function, the all except function will take a table.
In this case, I'll use the department's table, since that's where I want to modify our filter it.
It'll give us all of the filters removed except for those filters that we want to maintain the except
clause.
What this will let us do is selectively define which context filters will remain in place, in which
context filters to ignore.
That's a terribly confusing statement.
So let's visualize for a moment.
Our department's table has exactly three columns in a traditional setting with no special filtering
rules.
Like for our quantity, all three fields will be filtered based upon the context.
If we use the all function, then none of the three columns will be filtered and will always get all
of the department values at all times.
With the all except function, we can go somewhere between these two, we can specify which filters
we want to use versus which filters we don't want to use.
So using all except the department table and then the high section and the middle section will give
us all of the departments, except if the high or middle sections are filtered.
So this measure will use, calculate and take that quantity total what the filter context applied that
keeps filters on our high and our middle Middlefield.
I'll press, OK, and we can add our quantity measures to our pivot table to see if it's working at
the high level, we can see that our quantity and quantity of total sections match up.
That's a good sign since we would hope they would.
Once we drill down to the detail department level, we see a difference.
Our quantity total section has the total quantity for each of the sections and our quantity by department
is different and we want the ratio of those two fields.
Our last step is to recreate that quantity total section logic with our wages so that we have a proper
wage amount for each context entry.
I'll copy that quantity total section formula and create a new measure.
Once I paste it in, I'm going to change the quantity total measure piece to some of the wages.
Why don't I just use wages total since we have that already?
Well, as I mentioned, we're going to be changing our wages, total measure to take this value multiplied
by the ratio.
And so I don't want to introduce any circular logic.
This will now be the place where our wages amount is initially calculated from our underlying tables.
Once I've done this, we can go on to our wages total measure.
We could have put all of this logic into just this one measure.
However, splitting it out lets us check the quantity values and evaluate the individual steps along
the way.
Once they're defined, we can link them all together in a very nice, concise formula.
Here, I'll use Divide to calculate the fractional percentage of quantity that we want to allocate our
wages by, and then I'll multiply that with our wages total section measure and that's it.
Since all of our profit and cash on hand measures calculate using this wages total, our entire power
pivot infrastructure will stay in sync.
So let's go see what happens with our cash on hand.
Key performance indicators.
The first thing to notice is that the general section no longer has a huge negative cash on hand status
since there are no wages allocated to it anymore.
Instead, each of the underlying departments now has a lower cash on hand value since the wages are
taken out with dry goods even appearing to go negative.
Once I drop the quantity, Collins, the general section even disappears from our pivot table altogether,
since it no longer has data associated with it.
We've completely merged that wage data from the general entry across the other departments.
Naturally, there are always different ways to perform an allocation like this.
My selection of quantity is the determination factor was strictly a personal decision based upon a thought
process you could find and argue for different approaches based upon purchase order amounts or something
like allocating based upon square footage or simply selecting percentages to apply.
With that, we'll close out this lesson on advanced measures, and I'll bring you up to speed on the
exercise in the next video.