% Growth and Geometric Means

[Anonymous]

Hello,

I am having an issue with limitations with DAX functions. I have a hierarchical dataset made up of financial transactions. I want to be able to show average % growth over a period of years. 

I have a measure that can compute the % growth, year over year, for the last x years. This works fine. However, when I multiply them together and then try to raise them to a fractional exponents, BI kicks back an error saying the number is too big or too small.

This happens for all fractional exponents. I tried using the LOG function to get around this, but this has the same problem. The square function is useless because you can't even designate it to the Nth square.

Now, the Geomean and Geomeanx functions exist, but the problem here is that they work over a column.

My data is hierarchical. So my summary table is going to be annual % changes at the account level. But users might want to see average growth at the division or sub-division level. 

The problem here is that different accounts represent different shares of total annual spending, so a geometric mean of a column of all % variances is going to be wrong (right).

Is there any easy way to weight the geometric mean?

A simple measure seems way easier. The totals get rolled up to the correct level of analysis because they are inside the paratheses, then the % variance is calculated for each year, then the geomean is taken.

Since finance dashboards are common, I have to imagine there is an easier way to get at % growth. I am not even sure how to do it a hard way. I guess I would need a dynamic summary table inside a measure that calculates year over year variances for all the years, then Geomeanx the column of variances, while including an expression that weights the % change by the % of total spending in the column?

But then some accounts are new and have no data, and any geomean with a 0 is 0, so it seems like I might need to make a second summary table that summarizes total % variance by year, using the filtered data from the first table.

That seems crazy for what is a common measure. Hopefully I'm missing something?

[AlexisOlson]

I do geometric linking and annualization of % growth all the time in DAX without many issues. When I get that error, it's almost always because I've made a mistake in the measure somewhere.

The basic pattern is to construct a table of daily/monthly/annual returns and then use PRODUCTX to link them. Here's an example of a cumulative annualized return for a single return series.

Annualized Cumulative Return = 
VAR _CurrYear = MAX ( AnnualGrowth[Year] )
VAR _YearsToDate =
    CALCULATETABLE (
        AnnualGrowth,
        ALLSELECTED ( AnnualGrowth[Year] ),
        KEEPFILTERS ( AnnualGrowth[Year] <= _CurrYear )
    )
VAR _Years = COUNTROWS ( _YearsToDate )
RETURN
    PRODUCTX ( _YearsToDate, 1 + AnnualGrowth[Return] ) ^ ( 1 / _Years ) - 1

I've included this in the attached file as well. Preview:

[Anonymous · Accepted solution]

Thanks,  using the product function will tighten up my measure.

I figured out the problem was that BI has anything divided by zero as infinite, instead of undefined as per most conventions. But since some of my hierachies aren't in recent use, they had zeros as denominators, creating infinites. The use of the measure in visuals was causing both the log and power functions to say the visual couldn't be displayed because they can't be applied to infinities. Wrapping it in an IFERROR fixed it.

This occured because some of the data has zeros instead of blanks because there were entries into an out of an account, resulting in a zero balance instead of the account just being missing from the dataset for that year, so my if statements to dynamically use as many years of data as were availible weren't correctly pulling out all the zero values because they were just looking for BLANK().