cancel
Showing results for
Search instead for
Did you mean:
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?

2 ACCEPTED SOLUTIONS

Accepted Solutions

## Re: Average of %

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

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

## 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.
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 %

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

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

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

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!

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

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

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

Top Solution Authors
Top Kudoed Authors