Measure for annual average

[Anonymous]

Hi PBI community members,

I would like to calculate annual average and annual maximum in my various tables. In excel this is easily done, for example:

=AVERAGE(C2:C54)

=MAX(C2:C54)

But I’m not really sure how to do it in PBI now that I have transformed my data. I’ve looked at a couple of videos and read a few chats to no avail. I think using a Measure is the best approach, but I’m struggling to figure it out. 

I have columns with 48 LGAs (LGA). Each LGA has one data point (Combined Emp) for each financial year (Year) starting 2001-2002 to 2029-2030. 

What I would like to do is to have one new column showing the average for each financial year; and a second column showing the maximum for each financial year (please note that the Year column is text rather than numeric).

Any assistance would be appreciated. 

Brent 

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

Diversity Emp Ave&Max 1 = MAXX(filter(allselected(Diversity_Emp),[Year] =MAX([Year])),[Diversity Emp])

[amitchandak]

@Anonymous , if combined emp is column a new column

averageX(filter(Table,[year] =earlier([year])),[combined Emp])

if this is a measure - combined Emp , then a new measure

averageX(filter(allselected(Table),[year] =max([year])),[combined Emp])

[Anonymous]

Thanks @amitchandak. I've copied

averageX(filter(allselected(Table),[year] =max([year])),[combined Emp])

into PBI and it seems to be working. I was also hoping to display the maximum for each year. Does this formula also do that or is a different measure required to do that?

Cheers,

Brent

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file and show the expected result in a Table format very clearly.

[Anonymous]

Hi Ashish,

Thank you for your assistance. 

I have placed a link below, hopefully you will be able to download it. If you look at Page 3 "Economic diversity" I am trying to display the annual average and the annual maximum on the line graph in the bottom LHS. 

https://www.dropbox.com/s/ap9rrt9jdxty9bk/Ashish_Economic%20indicators.pbix?dl=0

Kind regards,

Brent

[Ashish_Mathur]

I still do not understand.  Take a simple Table as n example and on that show the expected result.

[Anonymous]

Hi @Ashish_Mathur,

Below is an Excel sheet of what we are wanting to show in PBI. It's very simple in Excel, but I'm not sure how to translate that into PBI. In the Excel sheet row 50 has the average for each year and row 51 the maximum values. 

In PBI all I am trying to do is make a measure that can show the average and max on a line graph. 

I really appreciate your assistance. 

Brent

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

Diversity Emp Ave&Max 1 = MAXX(filter(allselected(Diversity_Emp),[Year] =MAX([Year])),[Diversity Emp])

[Anonymous]

Hi @Ashish_Mathur 

Yes, when I use that and stick it onto a table it does work. However, when I use it in my line chart, and click on different regions of the linked map the Max measure changes. It should remain constant. 

I think the next thing I need to do is learn how to stop this from happening, it seems to be related to filters. Any tips appreciated!

Cheers,

Brent

[Ashish_Mathur · Accepted solution]

Hi,

A pattern like this should work

=calculate([Diversity Emp Ave&Max 1],all(Data[region]))

If it does not, then review your Diversity Emp measure