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

 

I already tried:
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
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:

 

1.PNG

 

Or you can try measure:

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

Thanks,
Xi Jin.

View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Re: Calculate Average per category

HI @mschultens

 

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

 

1.PNG

 

Or you can try measure:

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

Thanks,
Xi Jin.

View solution in original post

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

Re: Calculate Average per category

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

Helpful resources

Announcements
Community Blog

Community Blog

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

Using the Community

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

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors