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

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

10 REPLIES 10
Zubair_Muhammad
Super User
Super User

HI @mschultens

 

Try this MEASURE.

Earlier typically works in a Column not a MEASURE

 

AverageMeasure =
CALCULATE ( AVERAGE ( table[value] ), ALLEXCEPT ( Table, table[category] ) )

Regards
Zubair

Please try my custom visuals

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

@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


Regards
Zubair

Please try my custom visuals

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

@mschultens

 

Try your own formula as a calculated column.


Regards
Zubair

Please try my custom visuals

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.

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.

Hi

 

I'd like to use this solution, but I also have a subcategory and year that I have to take into account.

I tried using extra filters for subcategory and year, but keep getting errors.

Can someone help me?

This is what my table looks like

 

Knipsel.JPG

 

@v-xjiin-msft 

 

I have a question about average too

 

Delion_0-1613984839636.png

I have this data, what I want to achieve :

> Average Total : points PR+WAR from all technician

> Average PR & WAR : point only for PR OR only WAR based on FILTER

 

This is my code but gave me value :

YESTERDAY

> Average Total : correct value 7,5 this DAX gave me 3,75  (WRONG)

> Average PR & WAR : correct value PR = 3 and WAR = 4.5 this DAX gave me PR = 3 and WAR = 4.5 (CORRECT)

Bassicly how to make this DAX give a correct value for Average Total and Average PR & WAR 

 

1. AVG_Points = AVERAGE(CL_PROD[Points])

2 AVG_Points_Filter = IF(MAX(CL_PROD[WO_Type])= "PR" || MAX(CL_PROD[WO_Type])= "WAR",
	            [AVG_Points],[AVG_Points]*2)

 

 

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors