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
Highlighted

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors