cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Average of %

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?

 

2018-11-24_9-26-45.jpg

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Average of %

You need a matrix, not a table.

 

 temp.png

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)))

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Re: Average of %

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

10 REPLIES 10

Re: Average of %

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Re: Average of %

@MattAllingtonCould not find teh solution in your blog.

Re: Average of %

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Re: Average of %

Re: Average of %

You need a matrix, not a table.

 

 temp.png

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)))

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Average of %

Awsome

Anonymous
Not applicable

Re: Average of %

@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

Re: Average of %

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Anonymous
Not applicable

Re: Average of %

@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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors