Average of %

[Anonymous]

Hello,

Is there a way to get average of a meassure. I have a growth rate YoY by countries, is it possible to get AVG Growth Rate Yr over Year by Country?

[MattAllington · Accepted solution]

You need a matrix, not a table.

 

Then you need your formula to return nothing in year 2, otherwise your 100% in year 2 will skew the numbers.  Here is a formula

Prct Change Population = sumx(VALUES(Calender[Year]),
                            Var PY =  [Tot Pop Prv Yr]
                            return if(PY<>0,DIVIDE(([Total Pop] -  [Tot Pop Prv Yr]),[Total Pop],0)))

[MattAllington]

Yes it is possible. The cause is a bit hard to understand when you first start out. I have written a blog SUM vs SUMX that explains it. 

[Anonymous]

@MattAllingtonCould not find teh solution in your blog.

[MattAllington]

If you post a sample workbook, I will take a look. 

[Anonymous]

@MattAllingtonhttps://1drv.ms/u/s!AumyvtVXEve9jLwKng8FyK6ttVhLow

[MattAllington · Accepted solution]

You need a matrix, not a table.

 

Then you need your formula to return nothing in year 2, otherwise your 100% in year 2 will skew the numbers.  Here is a formula

Prct Change Population = sumx(VALUES(Calender[Year]),
                            Var PY =  [Tot Pop Prv Yr]
                            return if(PY<>0,DIVIDE(([Total Pop] -  [Tot Pop Prv Yr]),[Total Pop],0)))

[Anonymous]

@MattAllingtonChange in % is sum of all Percentages of Each Country. Example: Afghanistan Shows 14.41%. but actual average of 5 years should be

3.26 + 3.13 + 2.90 + 2.65 + 2.46 = 14.4 / 5 ( = 2.88). Is there a way to achive this?

Thanks

[MattAllington · Accepted solution]

Sorry, that was a silly mistake. Just change sumx to averagex 

[Anonymous]

@MattAllingtonis it possible to also Rank by % Change in Population, when I use RankX it gives me 1 for every row.

Rank by Prct Change = RankX(ALL(Population), [% Change in Population], , DESC)

I deally I would like user to provide # like Top 3, Top 5 etc.

[MattAllington]

This formula works as long as there is no year on the visual

Rank = RANKX(all(Population[Country Name]),[Prct Change Population])

[Anonymous]

Awsome