cancel
Showing results for
Did you mean:
Highlighted
New Member

## Calculate Average per category

Hello everyone,

I spent a lot of time already looking at similar problems to the one I have, but nothing really worked for me.  Assume I have a table with a category and some values.

How can I calculate a measure or a new column that shows the average of the values for one category like in my example below?

category | value | average

----------------------------

A | 2 | 1.5

A | 1 | 1.5

B | 3 | 2

B | 2 | 2

B | 1 | 2

AverageMeasure= CALCULATE(AVERAGE(table[value]); FILTER(table; table[category]=EARLIER(table[category]))

but it gives me an error saying that EARLIER refers to an earlier row context that does not exist. And when I put an explicit category in the FILTER statement, the result seems to be just the original value per row.

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Solution Sage

## Re: Calculate Average per category

Hi @mschultens,

Did you use the original expression in the calculated column? It works for me. Please refer: Or you can try measure:

```AverageMeasure =
CALCULATE (
AVERAGE ( Table3[Value] ),
FILTER ( ALLSELECTED ( Table3 ), Table3[Category] = MAX ( Table3[Category] ) )
)```

Thanks,
Xi Jin.

8 REPLIES 8
Highlighted Super User III

## Re: Calculate Average per category

Try this MEASURE.

Earlier typically works in a Column not a MEASURE

```AverageMeasure =
CALCULATE ( AVERAGE ( table[value] ), ALLEXCEPT ( Table, table[category] ) )```
Try my new Power BI game Cross the River
Highlighted Super User III

## Re: Calculate Average per category

@mschultens

You can simply use Average(table[value]) as well if you choose not to put VALUE in the Table VISUAL

Your formula would work as a calculated column

Try my new Power BI game Cross the River
Highlighted
New Member

## Re: Calculate Average per category

Thank you, but that does not work for me, because I want to put the different averages into one visualization.

Highlighted Super User III

## Re: Calculate Average per category

@mschultens

Try your own formula as a calculated column.

Try my new Power BI game Cross the River
Highlighted
New Member

## Re: Calculate Average per category

It gives me an error saying that a single value for category cannot be determined. I tried putting a MAX() in front, but that gives the same error.

Highlighted Solution Sage

## Re: Calculate Average per category

Hi @mschultens,

Did you use the original expression in the calculated column? It works for me. Please refer: Or you can try measure:

```AverageMeasure =
CALCULATE (
AVERAGE ( Table3[Value] ),
FILTER ( ALLSELECTED ( Table3 ), Table3[Category] = MAX ( Table3[Category] ) )
)```

Thanks,
Xi Jin.

Highlighted
New Member

## Re: Calculate Average per category

I had a mistake somewhere else in my model. Both solutions work fine! Thank you very much guys! 🙂

Highlighted Helper I

## Re: Calculate Average per category

Thank you!  That just helped me with a similar issue too.

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (968)