Forum Discussion
Measure for annual average
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
Hi,
Does this measure work?
Diversity Emp Ave&Max 1 = MAXX(filter(allselected(Diversity_Emp),[Year] =MAX([Year])),[Diversity Emp])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
9 Replies
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])
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
Hi,
Share the link from where i can download your PBI file and show the expected result in a Table format very clearly.
