Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

11 REPLIES 11
Zubair_Muhammad
Community Champion
Community Champion

HI @mschultens

 

Try this MEASURE.

Earlier typically works in a Column not a MEASURE

 

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

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

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.

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

 

I'm also intrested in this. Did you solved it?

@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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.