Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have filtered a certain range of years that present the following data:
year 2015 = 72
year 2016 = 77
year 2017 = 61
year 2018 = 97
I already have the following data:
variation in the period: 34.7%
variation in the last year: 6.5%
But I need to know how to arrive at the annual average, which in this case is equal to 10.4%.
Solved! Go to Solution.
Hi, @gustavofarias10
You can try the following methods.
Measure % =
Var _current=SUM('Table'[Value])
Var _Previous=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year])-1))
Return
DIVIDE(_current-_Previous,_current)
Average% =
Var _table=SUMMARIZE('Table','Table'[Year],"%",[Measure %])
Return
DIVIDE(SUMX(FILTER(_table,[Year]>=MIN('Table'[Year])&&[Year]<=MAX('Table'[Year])),[%]),MAX('Table'[Year])-MIN('Table'[Year]))
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @gustavofarias10
You can try the following methods.
Measure % =
Var _current=SUM('Table'[Value])
Var _Previous=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year])-1))
Return
DIVIDE(_current-_Previous,_current)
Average% =
Var _table=SUMMARIZE('Table','Table'[Year],"%",[Measure %])
Return
DIVIDE(SUMX(FILTER(_table,[Year]>=MIN('Table'[Year])&&[Year]<=MAX('Table'[Year])),[%]),MAX('Table'[Year])-MIN('Table'[Year]))
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @v-zhangti
Your solution ended up clarifying an idea that will help me solve this problem. Thank you very much.
Hi @gustavofarias10
Here is a DAX Formula:
CAGR =
VAR StartingYear = MIN('TableName'[YearColumn])
VAR EndingYear = MAX('TableName'[YearColumn])
VAR StartingValue = CALCULATE(SUM('TableName'[ValueColumn]), 'TableName'[YearColumn] = StartingYear)
VAR EndingValue = CALCULATE(SUM('TableName'[ValueColumn]), 'TableName'[YearColumn] = EndingYear)
VAR NumberOfYears = EndingYear - StartingYear
RETURN
IF(
NumberOfYears > 0,
POWER(EndingValue / StartingValue, 1 / NumberOfYears) - 1,
BLANK()
)
Please replace with the correct table names, and it should work.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
91 | |
46 | |
25 | |
24 | |
19 |