Forum Discussion
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 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.
but that does return an incorrect value (the excel one is the right one)
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.
5 Replies
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 )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.
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.
