cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mschultens
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
v-xjiin-msft 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
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
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
mschultens
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.

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

v-xjiin-msft 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

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

Re: Calculate Average per category

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

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors