Forum Discussion
Solved
Geometric mean based on measure wrong
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 Po...
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 )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.
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.
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.