Average Annualized percent

[aashton]

Hello,

I am trying to find the average annualized percent, presently in Excel I use the forumula:

SUM((columns Jan - current month)/number of months)*12.  So for October, I'm adding up Jan - Oct, dividing by 10, then multiplying by 12 to annualize it.  In my power BI I have:

Controllable Turnover is the measure I am trying to annualize

Calculated Field - Month Year Number = MONTH(Month Year) --- trying to get the month number

The problem I have is I can't use DIVIDE with the calculated field, can only divide with a measure.  

[v-cgao-msft]

Hi @aashton ,

Please new a measure like:

Measure = 
VAR _month = MAX('Calendar'[Month Number])
VAR _year = MAX('Calendar'[Year])
VAR _total = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Calendar'),'Calendar'[Year]=_year&&'Calendar'[Month Number]<=_month))
VAR _result = DIVIDE(_total,_month)*100
RETURN
_result

If only the latest value is needed:

Measure 2 = 
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
VAR _total = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Calendar'),'Calendar'[Year]=_year&&'Calendar'[Month Number]<=_month))
VAR _result = DIVIDE(_total,_month)*100
RETURN
_result

If I have misunderstood your needs, please feel free to contact me.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[aashton]

Thank you for your help.  Having trouble getting the _Total to work....My value is a measure, so it is saying i can't use Sum. .. So I tried below but am getting the Placeholder function error:

_Total = CALCULATE('CR HCM Monthly Data'[Controllable Turnover percentage], 'CR HCM Monthly Data'[Month Year Year Number]=[_Year])

[v-cgao-msft · Accepted solution]

Hi @aashton ,

//My value is a measure

Please try:

Measure = 
VAR _month = MAX('Calendar'[Month Number])
VAR _year = MAX('Calendar'[Year])
VAR _total = SUMX(FILTER(ALL('Calendar'),'Calendar'[Year]=_year&&'Calendar'[Month Number]<=_month),[Value])
VAR _result = DIVIDE(_total,_month)*100
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data