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

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.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors