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

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors