Welcome to Excel Magic Trick number 1,427.
Hey, if you want to download this Excel
workbook, either the start file or the finished files
so you could follow along, click on the link below the video.
Hey, we've got to talk about the DAX formula--
GEOMEANX function to calculate average compounding
rate per period.
Now any time you are given a number of percentage change
or growth rate percentages, in order to calculate the average
amount, you cannot use the arithmetic mean,
you have to use the GeoMean or the geometric mean.
Now I have two videos over here.
If you don't know why we have to use geometric mean
and why it's so important in finance and other fields,
I have this business analytics video
and this statistical analysis video from my classes
that highlight-- that explain in great detail how
awesome knowing how to use geometric mean is.
Now here are the two formulas, the point in this video
is simply to see how to do it in DAX.
Now if we scroll down, we can see we have one, two tables.
This table has the year of the actual percentage change,
and then the particular investment-- so for Fund A,
we invested in 2004 all the way to the current year 2008,
and these are all the percentage changes in the investment for--
let's see, that's Fund A.
For B, all of these right here.
There's the year, there's the percentage change.
We also have a second table that lists
the name of the investment and the initial investment.
Now I've already started the pivot table
and added both of these tables to the data model, so let's go
look--
Power Pivot, Data Model.
fInvestments, here are the three columns, and fStartInvestments.
If we go over to Diagram view, I have this other table here
with some calculations.
They're hidden for the time being, but at the end
the video I'll show where you can go.
Look at those if you want.
But our particular task is to add
a number of measures that will calculate the averaging
compound rate, the number of years for each investment, when
the investments started, and the current value.
Now let's go back over to Data view.
Over here to fInvestments.
Now our first calculation is going to be--
and I'm going to start typing, it shoots me up
to the formula bar.
Average Compounding Rate and parentheses GeoMean colon
equal sign, and we'll just use the GEOMEAN.
Now there's a GEOMEAN that if you
had a column with the rate of changes plus 1,
you could just use that, but we need
to iterate over that column and do a calculation,
so we're going to use GEOMEANX tab.
GEOMEANX, we need our table.
The name of our table is F-I--
Down Arrow to finvestments, comma, the expression.
Well, we need our column, F-I, and I need to down arrow
to Percentage Change Growth Rates.
Now in order to calculate the GeoMean,
you can't just use the percentage,
you have to use plus 1.
In essence, that's the original principal plus whatever
the rate of change was.
And that's our expression.
If we close parentheses, this will give us the growth factor.
If we hit Enter and go over to our pivot table,
Alt-Tab, Average Compounding Rate down here, when we add 1,
that's the actual amount that we can multiply by the start
amount to give us the ending amount if we raise it
to the correct number of periods.
Now in the pivot table report, I actually do not want 1 plus,
I want to show the actual average compounding rate.
So I'm going to Alt-Tab.
Simply click on the cell and we subtract 1.
Later we'll have to add it back in,
but for our report, Alt-Tab, that's what we want to see.
Now we could format that as a percentage if you wanted.
Alt-Tab, click on the cell, and that percentage button,
unlike over in Excel, will add a percentage format
with two decimals.
Over in Excel it has no decimals showing.
Alt-Tab, and there are our formatted average compounding
rates.
Now at the end we're going to amend our formulas to get rid
of the grand totals, but for right now
we will leave it like that.
Now if we think about how DAX made this calculation right,
that Bond Fund C will-- there's our investment table
with our initial investments.
That filter right there properly is from the start investment
table dragged down to rows.
So when in our pivot table, that calculation right there
sees this criteria, Bond Fund, this table
is filtered down to a single row, which then filter down
this table just down to our Bond Funds C,
grabbing only the rates of change
for that particular fund, and then the GeoMean
calculates the correct average compounding rate.
Now we're going to need a column for a number of years.
Alt-Tab.
So right below our average compounding rate
we'll type Number of Years Invested, colon, equal sign,
and guess what?
All we have to do is use the COUNTROWS function.
F-I, the actual investment.
Remember, in the pivot table when
it's on a particular investment, that filter
flows across the relationship and filters
the fInvestment table down to just the correct rows which
will give us exactly the number of years-- close parentheses
and Enter.
Alt-Tab, now we can drag our DAX formula down to Values.
Now notice, I completely dragged the wrong formula,
but this is a good opportunity to show you,
I did a video on this a couple weeks ago,
but implicit measures are automatically
created when you drag and drop a field down to the Values area,
and that's not what we want.
I'm going to uncheck that, go back over here.
It's not even showing over here.
It should be.
But if we come over to Advanced and say Show Implicit Measures,
I go over to the fStartInvestments, there it is.
I'm in our right-click, Delete.
It's going to say, Should I delete this from this model?
Yes.
So mistakes are good if we can learn from them.
So implicit measures, if you accidentally drag a field,
there will be an extra hidden measure over there,
and if you want to get rid of it,
you have to show implicit measures and then delete.
Here's the correct DAX formula, I drag it down here,
and there are the number of years.
Alt-Tab, our next calculation is the start year.
Start Year, colon, equal sign, and guess what?
I can look through the year column using the MIN function.
F-I, I want to go down to--
luckily they have icons here, that's a measure,
this is a column.
Investment Year, Tab.
Close parentheses and Enter.
Alt-Tab, and we can drag our start year over to Values.
There's the start year.
Our last calculation is going to be the current value.
Again, this video-- these videos up here show you, here's
the basic formula for calculating future value,
we're going to use that formula.
Alt-Tab.
Click down below Start Year.
Current value, colon, equal sign.
Now I would like to do F-S and then the start column
for investment amount, initial investment,
but you cannot put a naked column into a DAX measure.
Now remember, that table only has
one value for each investment, so I
have to use some aggregate function,
I'm going to use the SUM function.
Now I want to do F-S, I can see that I
have the option of columns now, not just a table.
It's Initial Investment, Tab, close parentheses--
so we're using SUM just to get out that column.
The filter context obviously will filter down
to a single row and we'll get the investment--
the initial investment for each fund.
Now we multiply it in parentheses 1
plus the average compounding rate per period.
That is a measure, so I type a square bracket.
There it is, Average Compounding Rate GeoMean.
By the way, these other measures are from the other model
that I built in here, so this is the one we want.
Same with these, these are from the other model, Tab.
Now close parentheses, that will not
do it, because the whole point of Average Compounding
Rate plus 1 is you need to then raise it
to the number of periods.
Now I already have that calculation,
so I caret, raise 2, square bracket,
and it's Number of Years Invested, Tab,
and that will work.
Enter.
I could format it.
Home.
Add some sort of currency.
Alt-Tab, now we simply drag Current Value
down to the Values, expand the columns,
and there is the current value for each one
of our investments using the correct average compounding
rate per period.
Now I want to go over and turn off the grand totals.
Notice, the logic for us for each measure here
will be if this table up here is filtered down to a single item,
then please run the formula.
Otherwise when it gets down here for the grand total,
the whole table is unfiltered, so they'll
have more than one row, then we'll show nothing.
Alt-Tab, I'm going to start with the first one right
before GeoMean.
If and then HASONEVALUE.
F-S, I'm going to do investment.
Close parentheses.
So the logical test doesn't have one value;
when it comes out true, comma, then please run the formula.
We leave off the last argument n IF over here in DAX--
if we want the blank function, to run and actually display
a blank.
Now I'm going to use this over and over,
so I'm holding Shift, clicking right before the I
for if, Control-C, and I come to the end.
Close parentheses and Enter.
Now I want to do that for each one of these.
Right before the C, Control-V, come to the end,
close parentheses.
Right before the M, Control-V, close parentheses and Enter.
Right before the S, Control-V, close parentheses, and Enter.
Now we go back over, and there is our report using
DAX and the GEOMEANX function.
All right, that was a little bit of fun with geometric mean.
We saw how to use GEOMEANX even if n has one value.
We saw how to use COUNTROWS, the MIN, and even a manual formula
for future value of an investment.
I mentioned earlier in the video about that other model
I had here.
You highlight row 20 and 43, right-click down to on Unhide,
there are the measures that I created.
You can go over and look in the model and look at pivot table
here.
All right, that was a little bit of fun with DAX GEOMEANX
and making a future value calculation.
We'll see you next video.