Geometric mean based on measure wrong

[stilllearning_]

I am sure powerbi can do what excel can 🙂 I am just doing it wrong.

Goal: calculate the geometric mean for a group of return values.
I have the values, but fail to get the correct geomean in Power BI.
I know that in year 1 I have no value for return and must exclude blanks.
There is of course the alternative, multiply all year values = F, then power (F, (1/number of values not blank)). but that is just the same what geomean does.
1. How do I solve it

2. Where am I going wrong in my thinking of the measure
Excel:

Power BI:

I tried first, but expect I need a table instead a measure.

TWR1 = GEOMEANX ('TWR Model','TWR Model'[GeoMeanvalues])
but that does return an incorrect value (the excel one is the right one)

TWR2 =

var _ret = [GeoMeanvalues]

RETURN

GEOMEANx(

    filter(

            SUMMARIZE(

                data, Data[Year End],

                "return", _ret),

                [return] <> BLANK()

    ),

    [return]

 ) -1

[AlexisOlson · Accepted solution]

You've got a couple of issues. First, you calculate _ret once and use that same single value for each year. Second, I think you need 1 + _ret inside your GEOMEANX.

Try this:

TWR =
VAR _YearReturns_ =
    ADDCOLUMNS ( VALUES ( Data[Year End] ), "@Return", [Return] )
VAR _NonBlank_ =
    FILTER ( _YearReturns_, NOT ISBLANK ( [@Return] ) )
VAR _Geomean =
    GEOMEANX ( _NonBlank_, 1 + [@Return] )
RETURN
    IF ( NOT ISBLANK ( _Geomean ), _Geomean - 1 )

[Anonymous]

Hi, how did you define the geomean values column? or is it a measure, anyway can you give more information

[AlexisOlson · Accepted solution]

You've got a couple of issues. First, you calculate _ret once and use that same single value for each year. Second, I think you need 1 + _ret inside your GEOMEANX.

Try this:

TWR =
VAR _YearReturns_ =
    ADDCOLUMNS ( VALUES ( Data[Year End] ), "@Return", [Return] )
VAR _NonBlank_ =
    FILTER ( _YearReturns_, NOT ISBLANK ( [@Return] ) )
VAR _Geomean =
    GEOMEANX ( _NonBlank_, 1 + [@Return] )
RETURN
    IF ( NOT ISBLANK ( _Geomean ), _Geomean - 1 )

[stilllearning_]

Your suggestion also works, IF i reference the dates table instead the data table.
I had ommited the model in my initial post, so that information was missing.

Appologies.

[stilllearning_]

By the way, I cannot add the pbix file, file type not supported, perhaps my level too low to post attachements. Can share via cloud link if that makes it easier.

[stilllearning_ · Accepted solution]

I think I got it.

I changed the geomeanx to the dates table instead the data table and it worked.

TWR test=

GEOMEANX('Dates of Valuation', [GeoMeanvalues])-1
yes geomean needs pos values only so +1 is needed, i had backed that into geomeanvalues.